Database and SQL Concepts Final Exam Study Guide

________ describes the large volume of data produced by every digital process, system, sensor, mobile device, and even social media exchange.

big data

For a one-to-many relationship in the Access Relationships window, how can you tell which table is the "many" table?

An infinity symbol identifies the "many" table.

Which of the following operators allows you to specify a range and is often used with date criteria?

BETWEEN

Which function calculates the number of records in a table?

COUNT

Which of the following SQL commands creates an index named LastName on the LastName field in the Customers table?

CREATE INDEX LastName ON Customers (LastName);

When you can't determine the value of a field using information from existing fields in a record in a query, you should create a(n) __________.

Computed Field

A ______________in a record is the result of a calculation from the rest of the record.

Computed fiekd

Which of the following access functions can you use to return the number of records in a group of records?

Count

What can you do to provide physical security for a database?

Create a backup of the database

How do you create a view of data in Access?

Create and run a query.

What type of software programs are Access, Oracle, DB2, MySQL, and SQL Server?

DBMSs

Which of the following is a drawback of flat files?

Data partitioned into several separate files can be difficult to summarize.

Which Access tool provides details about each table, query, report, form, and other object in a database?

Database Documenter

In Access, a(n) _________ query permanently removes all the records from the selected table(s) that satisfied the criteria entered in the query.

Delete

Which of the following fields would be the best candidate for a primary key field in a table of employee data?

EmployeeID

When you use a name containing a space in Access SQL, what must you do to specify the table or column name?

Enclose it in [square brackets].

Which part of a SQL query specifies the table names that contain the data you want to display in the query results?

FROM clause

(True/False) An employee's last name is an example of an attribute.

False

(True/False) To select data from more than one table at the same time, you need to perform a make-table query.

False

Which of the following are screen objects, used to maintain, review, and print data from a database?

Forms

In a query that uses an aggregate function, which SQL clause do you use to include criteria that limits the number of records selected for the query?

HAVING

What type of join should you use to create a query from a parent table named Customers and a child table named Orders where the linking field contained values in both tables?

INNER

What SQL keyword do you use in a query that saves the results of the query as a table?

INTO

Which of the following statements is correct?

In a flat-file environment, data is partitioned into several disjointed systems, lists, and files.

In the Access QBE, where do you specify criteria for a query?

In the criteria row of the query design grid.

Which of the following is a popular software stack?

LAMP

What type of access query creates a new table using the query results?

Make-table

When you display the system tables in Access, where do they appear?

Navigation Pane

Which of the following SQL commands sorts records by ProductName within ProductType?

ORDER BY ProductType, ProductName

To specify the CustomerNum field as the primary key for the Customer table, which clause should you use?

PRIMARY KEY (CustomerNum)

What does the INNER JOIN command select from related tables?

Records that have matching values in both tables.

When you create a query using SQL View to display fields and records, Access automatically places the _______________ keyword in the query window.

SELECT

Based on the SQL shown, which query lists the name and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000? Customers ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

SELECT CustomerName, Balance FROM Customers WHERE Balance BETWEEN 2000 AND 5000;

Based on the SQL shown, which query lists the name and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000?Customers ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

SELECT CustomerName, Balance FROM Customers WHERE Balance BETWEEN 2000 AND 5000;

Based on the SQL shown, which query lists the name and available credit for all customers with credit limits that exceed their balances? Customers ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

SELECT CustomerName, CreditLimit - Balance AS AvailableCredit FROM Customers WHERE CreditLimit > Balance;

Based on the table description and SQL shown, which query finds the description and price of the items that have a Price field value greater than Wireless charger, ItemNum W208 ?Items (ItemNum, Description, OnHand, Category, Storehouse, Price )

SELECT Description, Price FROM Items WHERE Price > (SELECT Price FROM Items WHERE ItemNum = 'W208');

Based on the SQL shown, which query lists the last names and bonuses of employees who have a Bonus field value greater than Juliana Suarez, EmployeeID 14? Employees ( EmployeeID, LastName, FirstName, HireDate, JobTitle, Bonus )

SELECT LastName, Bonus FROM Employees WHERE Bonus > (SELECT Bonus FROM Employees WHERE EmployeeID = '14') ;

Based on the SQL shown, which query selects the LastName, JobTitle, and increased bonus for all employees with a job title of SalesRep? The increased bonus is calculated as $100 more than the current bonus. Employees ( EmployeeID, LastName, FirstName, HireDate, JobTitle, Bonus )

SELECT LastName, JobTitle, Bonus + 100 AS IncreasedBonus FROM Employees WHERE JobTitle = 'SalesRep';

Based on the SQL shown, for each sales rep, which query lists the rep number, the number of customers assigned to the rep, and the average balance of the rep's customers? Group and order the records by rep number.Customers ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

SELECT RepNum, COUNT(*), AVG(Balance) FROM Customers GROUP BY RepNum ORDER BY RepNum;

Based on the SQL shown, for each sales rep, which query lists the rep number, the number of customers assigned to the rep, and the average balance of the rep's customers? Group and order the records by rep number. Customers ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

SELECT RepNum, COUNT(*), AVG(Balance) FROM Customers GROUP BY RepNum ORDER BY RepNum;

Which of the following is a popular relational database management system?

SQL Server

In access query, how can you list records in A-Z order by last name?

Set an ascending short order on the LastName field.

The view used to sort records is called the _______.

Sort key

Which of the following is a reason to store business data in a database rather than a spreadsheet?

Spreadsheets have limited data sharing and security features

Which aggregate function do you use to total values in a numeric field in a group of records?

Sum

Which SQL commands do you use to update the values of an existing field?

UPDATE and SET

What SQL command can you use to join two tables?

WHERE

Which SQL keyword can you use to join two tables that have a one-to-many relationship?

WHERE

Which command joins the related Customers and Orders tables based on the common CustomerID field?

WHERE Customers.CustomerID = Orders.CustomerID

Which clause has the same result as the clause WHERE Clients.ClientID = Projects.ClientID?

Which clause has the same result as the clause WHERE Clients.ClientID = Projects.ClientID?

In Access, if you want to be able to enter different criteria each time you run a query, create a(n) _____ query.

parameter

In Access, what uniquely identifies a row in a table?

primary key

If you are sorting records by more than one field, which field is the first field that is evaluated for sorting purposes?

primary sort key

A __________ is a question about the data structured in a way that the DBMS can recognize and process.

query

Which term can be described as the duplication of data and storing of data in multiple locations?

redundancy

A database that enforces _____ prevents users from creating orphan records.

referential integrity

A(n) ___________ can store information about multiple types of entities and the relationships among the entities.

relational database

When you place one query inside another, the inner query is called a(n) ___________________ and is evaluated first.

subquery

When do you use the & operator in an expression in Access Query Design View?

to combine values in character fields.

When would you use the SQL RIGHT JOIN command?

to find orphan records

A(n) _____ is an action that occurs automatically in response to a database operation such as INSERT, UPDATE, or DELETE.

trigger

Which database term refers to a row in a table?

tuple

In what type of SQL operation must two tables have the same field structure?

union

What type of Access query changes data?

update

What type of security is not automatically available in an Access database?

user-level security

You can create compound criteria (conditions) in a query by using which of the following two keywords?

AND, OR

What happens when you use the DROP TABLE command?

Access deletes the table structure and the table data.

What happens when you run a select query in Access?

Access selects current data from underlying tables

How are aggregate functions different from other types of functions?

Aggregate functions make calculations on groups of records.

If you will frequently sort on a field, it is a good candidate for an index.

True

In Access SQL, you use the asterisk (*) as a wildcard character to represent any number of characters.

True

How are entities represented in a relational database?

each entity is stored as a table

Which of the following is a visual way to represent and analyze a database?

entity-relationship (E-R) diagram

What do you call the field used to join two tables on the many side of a one-to-many relationship?

foreign key field

In an Access query, a "group" of records _____.

has the same value in a particular field

Which of the following is a problem that data redundancy causes?

increased data errors and inconsistencies

Which type of rule prevents the creation of orphan records?

integrity constraint

To insert a new field in a table, which keyword should be used with the ALTER TABLE command?

ADD

Which of the following SQL commands do you use to change the structure of the Vendors table?

ALTER TABLE Vendors

If you use _____ criteria, each criterion must be true for a record to be selected.

AND

Which of the following criteria select all records containing a value greater than 250?

>250

What do two tables use to link related records from each table?

A common field

Microsoft access is well-suited to which of the following cases?

A few trusted users work with the database at the same time.

In general, what should be in place before you select fields from more than one table in Access Query Design View?

The tables should be related in a proper one-to-many relationships.

What does the Bureau of Labor Statistics project for database administration jobs?

They will grow 11 percent through the year 2026.

(True/False) Access is considered a DBMS.

True

(True/False) In Access, you can display the results of a select query using the Run button or the View button.

True

(True/False) The attributes of an entity become the fields or columns in a table.

True

(True/False) and entity is a person, place, event, item, or other transaction for which you want to store and process data.

True

(True/False) redundancy makes changing data, more cumbersome and time-consuming.

True

(True/False) redundancy refers to the duplication of data, or the storing of the same data in more than one place.

True

A primary key field should never allow full values.

True

What is UNIX?

a popular open-source operating system for servers

What type of functions are Count, Sum, Avg, Max, and Min?

aggregate

What would you most likely use in SQL to perform calculations on groups of records?

aggregate functions

A(n) _______ is a set of rules, calculations, and assumptions used to solve a problem.

algorithm

What is the term for a general set of skills that involves defining, dissecting, communicating, and solving problems using data and information?

data analysis

What is the term for a collection of data organized in a manner that allows access, retrieval, and use of the data?

database

During which phase does a database developer create the entities, attributes, and relationships between the tables of data?

database design