Deck 7: Database Management Systems Processes and Services

Question 1

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

A)SELECT clause
B)WHERE clause
C)WITHIN clause
D)FROM clause

Answer 1

D

Question 2

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

A)is rarely sorted
B)always contains numeric fields
C)has the same value in a particular field
D)is a good candidate for a subquery

Answer 2

C

Question 3

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

A)BETWEEN
B)NOT
C)LIKE
D)IN

Answer 3

A

Question 4

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

A)UPDATE and SET
B)LIKE and IN
C)FROM and WHERE
D)INSERT and INTO

Question 5

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

Question 6

SQL uses an on-screen form to create tables, update tables, and retrieve data from tables.

Question 7

When you connect query conditions using the AND operator, only one of the conditions must be true for a particular record to be selected.

Question 8

While not always required, how should you end an SQL command?

A)with a comma (,)
B)with a period (.)
C)with a colon (:)
D)with a semicolon (;)

Question 9

Fields appear in alphabetical order based on fieldname in the query results of a SELECT clause.

Question 10

Which of the following is currently considered the most sought-after language for programmers?

A)Visual Basic
B)SQL
C)COBOL
D)CSS

Question 11

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?

A)RESTRICTING
B)GROUPING
C)JOINING
D)HAVING

Question 12

In Access SQL, which operator do you use in the WHERE clause when you want to include a wildcard in the criterion?

A)LIKE
B)AS
C)BETWEEN
D)UNION

Question 13

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

A)union
B)join
C)concatenation
D)extract

Question 14

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

A)Boolean operators
B)aggregate functions
C)data definition language
D)lookup functions

Question 15

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

A)to match a specific list of criteria
B)to reverse the result of the original condition.
C)to combine values in character fields
D)to sum values in numeric fields

Question 16

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

A)Enclose it in "quotation marks".
B)Enclose it in [square brackets].
C)Enclose it in *asterisks*.
D)Enclose it in (parentheses).

Question 17

What SQL command can you use to join two tables?

A)CONTAINS
B)WHERE
C)EXTRACT
D)MERGE

Question 18

When a SQL statement consists of two queries and you place one query inside another, the inner query becomes a(n) _____.

A)aggregate query
B)Boolean query
C)compound query
D)subquery

Question 19

Which of the following SQL statements selects ProductDescription and Price fields for every record in the Products table with a Price value greater than $50.00?

A)SELECT ProductDescription, Price FROM Products WHERE Price > 50;
B)SELECT ProductDescription, Price > 50 FROM Products;
C)SELECT ProductDescription, Price FROM Products WITH Price > 50;
D)SELECT ProductDescription, Price FROM Products WHERE Price < 50;

Question 20

A good name for an Access table might be tbl!Student.

Question 21

Which function calculates the number of records in a table?

A)COUNT
B)SUM
C)MAX
D)MIN

Question 22

Based on the table description and SQL shown, which query lists the states in the Students table and displays the number of students from each state? Students (StudentID, FirstName, LastName, Street, City, State, PostalCode)

A)SELECT State, COUNT(*) AS CountOfState FROM Students ORDER BY State;
B)SELECT State, COUNT(*) AS CountOfState FROM Students HAVING State;
C)SELECT State, COUNT(*) AS CountOfState FROM Students;
D)SELECT State, COUNT(*) AS CountOfState FROM Students GROUP BY State;

Question 23

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 )

A)SELECT LastName, Bonus FROM Employees WHERE Bonus >(SELECT Bonus FROM Employees WHERE EmployeeID = '14');
B)SELECT LastName, Bonus FROM Employees HAVING Bonus > EmployeeID = '14';
C)SELECT LastName, Bonus FROM Employees WHERE Bonus > UNION (SELECT LastName, Bonus FROM Employees WHERE EmployeeID = '14');
D)SELECT LastName, Bonus FROM Employees WHERE Bonus > UPDATE SET EmployeeID = '14';

Question 24

What SQL command do you use to add new data to a table?

A)INSERT
B)APPEND
C)ADDTO
D)SELECT

Question 25

Based on the table description and SQL shown, which query lists the first name and last name of all students, separated by a space? ​
Students (StudentID, FirstName, LastName, Street, City, State, PostalCode)

A)SELECT FirstName & LastName AS FullName FROM Students;
B)SELECT FirstName & ' ' & LastName AS FullName FROM Students;;
C)SELECT FirstName ' ' LastName AS FullName FROM Students;
D)SELECT FirstName, LastName AS FullName FROM Students;

Question 26

In Access SQL, which operator could you use to list all fields from a Customers table where the City field is Madison, Middleton, or Belleville?

A)LIKE
B)IN
C)BETWEEN
D)AND

Question 27

Which query will return the number of rows from the Customers table where the State field is 'AZ'?

A)SELECT FROM Customers WHERE State='AZ' COUNT(*);
B)SELECT COUNT(*) FROM Customers WHERE State='AZ';
C)SELECT FROM Customers COUNT(*) WHERE State='AZ';
D)SELECT COUNT(*) WHERE State='AZ' FROM Customers;

Question 28

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 )

A)SELECT CustomerName AS AvailableCredit FROM Customers WHERE CreditLimit > Balance;
B)SELECT CustomerName, CreditLimit AS AvailableCredit FROM Customers WHERE CreditLimit > Balance;
C)SELECT CustomerName, Balance AS AvailableCredit FROM Customers WHERE CreditLimit > Balance ;
D)SELECT CustomerName, CreditLimit - Balance AS AvailableCredit FROM Customers WHERE CreditLimit > Balance;

Question 29

Based on the SQL shown, which query finds how many items are in the TOY category? Items (ItemNum, Description, OnHand, Category, Storehouse, Price )

A)SELECT SUM(*) FROM Items WHERE Category='TOY' ;
B)SELECT COUNT(*) FROM Items WHERE Category='TOY' ;
C)SELECT COUNT FROM Items WHERE Category='TOY' ;
D)SELECT COUNT* FROM Items WHERE Category='TOY' ;

Question 30

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 )

A)SELECT LastName, JobTitle, Bonus FROM Employees WHERE JobTitle = 'SalesRep' AND IncreasedBonus = Bonus + 100;
B)SELECT LastName, JobTitle, Bonus AS IncreasedBonus + 100 FROM Employees WHERE JobTitle = 'SalesRep';
C)SELECT * FROM Employees WHERE IncreasedBonus = Bonus + 100;
D)SELECT LastName, JobTitle, Bonus + 100 AS IncreasedBonus FROM Employees WHERE JobTitle = 'SalesRep';

Question 31

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

A)joined query
B)aggregate query
C)subquery
D)compound query

Question 32

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

A)SORT BY ProductType, ProductName
B)LIST ProductName IN ProductType
C)ORDER BY ProductName IN ProductType
D)ORDER BY ProductType, ProductName

Question 33

The Clients table contains 12 records with three different values in the PostalCode field. If you create a query that groups the records based on the PostalCode field, how many records do the results contain?

A)12
B)36
C)3
D)4

Question 34

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 )

A)SELECT RepNum, AVG(Balance) FROM Customers GROUP BY RepNum ORDER BY RepNum;
B)SELECT RepNum, COUNT(RepNum), AVG(Balance) FROM Customers GROUP BY RepNum ORDER BY RepNum;
C)SELECT RepNum, COUNT(*), AVG(Balance) FROM Customers GROUP BY RepNum ORDER BY RepNum;
D)SELECT RepNum, COUNT(*) FROM Customers GROUP BY RepNum ORDER BY RepNum;

Question 35

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 )

A)SELECT CustomerName, Balance FROM Customers WHERE Balance BETWEEN 2000 AND 5000;
B)SELECT CustomerName, Balance FROM Customers WHERE Balance > 2000;
C)SELECT Balance FROM Customers WHERE Balance BETWEEN 2000 AND 5000;
D)SELECT CustomerName, Balance FROM Customers WHERE Balance BETWEEN 2000 OR 5000;

Question 36

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

A)UPDATE
B)INSERT
C)INTO
D)DELETE

Question 37

Which statement creates a table named PostalCodes with a five-character PostalCode text field and a two-character Abbrev text field?

A)CREATE TABLE PostalCodes (PostalCode TEXT, Abbrev TEXT);
B)CREATE TABLE PostalCodes (PostalCode (TEXT 5), Abbrev (TEXT 2));
C)CREATE TABLE PostalCodes (PostalCode TEXT (5), Abbrev TEXT (2));
D)CREATE TABLE (PostalCode TEXT (5), Abbrev TEXT (2));

Question 38

Based on the table description and SQL shown, which query increases the FeeAmount by 2 percent? Fees ( FeeID, FeeType, FeeAmount )

A)UPDATE Fees SET FeeAmount = FeeAmount * 1.2;
B)UPDATE Fees SET FeeAmount = FeeAmount * 2%;
C)UPDATE Fees SET FeeAmount = FeeAmount * 1.02;
D)SELECT Fees WHERE FeeAmount = FeeAmount * 1.02;

Question 39

What SQL command do you use to make changes to existing data in a table?

A)MODIFY
B)CHANGE
C)SELECT
D)UPDATE

Question 40

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 )

A)SELECT Description, Price FROM Items WHERE Price >(SELECT Price FROM Items WHERE ItemNum = 'W208');
B)SELECT Description, Price FROM Items WHERE Price > ItemNum = 'W208');
C)SELECT Price FROM Items WHERE ItemNum > 'W208' (SELECT Description, Price FROM Items);
D)SELECT Description, Price FROM Items WHERE Price > (SELECT ItemNum = 'W208' FROM Items);

Question 41

List five Access data types and their corresponding SQL data types.

Question 42

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

A)SELECT
B)UPDATE
C)CREATE
D)FIND

Question 43

Why is SQL a highly sought-after language for programmers?

A)SQL is difficult to learn because it does not use English-like commands.
B)SQL is a universal language for selecting and maintaining data in a relational database.
C)SQL provides a foundation for learning about artificial intelligence.
D)SQL is a popular language for manipulating graphical objects.

Question 44

How are compound SQL criteria conditions formed?

Question 45

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

A)LINK
B)RELATE
C)UNION
D)WHERE

Question 46

How can you improve your SQL skills?

A)Practice using online SQL tutorials.
B)Create Access queries exclusively in Query Design View.
C)Take a course in JavaScript.
D)Use a search engine to conduct searches for general information.

Question 47

Explain how to use three SQL clauses to select fields and join tables using the WHERE keyword in SQL.

Question 48

Explain the requirements of two sets of records involved in a union.

Question 49

What are five restrictions placed on table and field names in Access?

Question 50

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

A)WHERE CustomerID.Customers = CustomerID.Orders
B)JOIN Customers.CustomerID = Orders.CustomerID
C)WHERE Customers.CustomerID = Orders.CustomerID
D)SELECT Customers.CustomerID AND Orders.CustomerID