Chapter 3 Practice Quiz
Based on the code shown, which query deletes any row in the OrderLine table in which the item number is MT03? OrderLine (OrderNum, ItemNum, NumOrdered, QuotedPrice)Item (ItemNum, Description, OnHand, Category, Storehouse, Price ) A. DELETE FROM OrderLine WHERE ItemNum='MT03' ; B. DELETE FROM OrderLine WHERE ItemNum=MT03 ; C. REMOVE FROM OrderLine WHERE ItemNum='MT03' ; D. REMOVE FROM OrderLine WHERE ItemNum=MT03 ;
A
Based on the code shown, which query lists the descriptions of all items that are located in Storehouse3 and for which there are more than 20 units on hand? Item (ItemNum, Description, OnHand, Category, Storehouse, Price ) A. SELECT Description FROM Item WHERE Storehouse='3' AND OnHand>20 ; B. SELECT Description FROM Item WHERE Storehouse='3' OR OnHand>20 ; C. SELECT Description FROM Item WHERE Storehouse='3' ; D. SELECT Description FROM Customer WHERE Storehouse='3' AND OnHand>20 ;
A
Based on the code shown, which query lists the number, name, and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000? Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum ) A. SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ; B. SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE Balance > 2000 ; C. SELECT CustomerName, Balance FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ; D. SELECT CustomerNum, CustomerName FROM Customer WHERE Balance BETWEEN 2000 AND 5000 ;
A
What command do you use to add new data to a table? .A. INSERT B. APPEND C. ADDTO D. SELECT
A
When used after the word SELECT, which symbol indicates that you want to include all fields in the query results in the order in which you described them to the DBMS when you created the table? A. * B. & C. # D. $
A
Which function calculates the number of entries in a table? A. COUNT B. SUM C. MAX D. MIN
A
Which operator do you use in the WHERE clause when you want to include a wildcard? A. LIKE B. AS C. BETWEEN D. UNION
A
Based on the code shown, which query finds how many items are in category TOY? Item (ItemNum, Description, OnHand, Category, Storehouse, Price ) A. SELECT SUM(*) FROM Item WHERE Category='TOY' ; B. SELECT COUNT(*) FROM Item WHERE Category='TOY' ; C. SELECT COUNT FROM Item WHERE Category='TOY' ; D. SELECT COUNT* FROM Item WHERE Category='TOY' ;
B
Based on the code shown, which query lists the descriptions of all items that are located in Storehouse 3 or for which there are more than 20 units on hand, or both? Item (ItemNum, Description, OnHand, Category, Storehouse, Price ) A. SELECT Description FROM Customer WHERE Storehouse='3' AND OnHand>20 ; B. SELECT Description FROM Item WHERE Storehouse='3' OR OnHand>20 ; C. SELECT Description FROM Item WHERE Storehouse='3' ; D. SELECT Description FROM Item WHERE OnHand>20 ;
B
Based on the code shown, which query lists the name of every student whose postal code is 10113? Student (StudentID, FirstName, LastName, Street, City, State, PostalCode) A. SELECT FirstName, LastName WHERE PostalCode='10113' ; B. SELECT FirstName, LastName FROM Student WHERE PostalCode='10113' ; C. SELECT FirstName, LastName FROM Student WHERE PostalCode='10113' D. SELECT * FROM Student WHERE PostalCode='10113' ;
B
Based on the code shown, which query lists the number and name of all customers that are either represented by sales rep 30 or that currently have orders on file, or both? Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum ) A. SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer ; D. SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders WHERE Customer.CustomerNum=Orders.CustomerNum ; C. SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' WHERE Customer.CustomerNum=Orders.CustomerNum ; D. SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum='30' UNION SELECT Customer.CustomerNum, CustomerName, FROM Customer, Orders ;
B
Based on the code shown, which query lists the number, name, and complete address of every customer located on a street that contains the letters "Oxford"? Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum ) A. SELECT CustomerNum, CustomerName, Street, City, State, PostalCode FROM Customer WHERE Street LIKE "?Oxford" ; B. SELECT CustomerNum, CustomerName, Street, City, State, PostalCode FROM Customer WHERE Street LIKE "%Oxford%" ; C. SELECT CustomerNum, CustomerName, Street, City, State, PostalCode FROM Customer WHERE Street LIKE "@Oxford@" ; D. SELECT CustomerNum, CustomerName, Street, City, State, PostalCode FROM Customer WHERE Street LIKE "Oxford" ;
B
Based on the code shown, which query lists the number, name, credit limit, and balance for all customers with credit limits that exceed their balances? Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum ) A. SELECT CustomerNum, CustomerName FROM Customer WHERE CreditLimit>Balance ; B. SELECT CustomerNum, CustomerName, CreditLimit, Balance FROM Customer WHERE CreditLimit>Balance ; C. SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE CreditLimit>Balance ; D. SELECT CustomerNum, CustomerName, CreditLimit, Balance FROM Customer ;
B
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 question marks
B
Which query will return the number of rows where the State field is 'AZ' from the table Customers? 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;
B
In versions of SQL other than Access, which character is used as a wildcard to represent any collection of characters? A. asterisks (*) B. percent sign (%) C. underscore (_) D. hash tag (#)
B OR C
Based on the code 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? The records are grouped by rep number and ordered by rep number. Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum ) A. SELECT RepNum, AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ; B. SELECT RepNum, COUNT(*), AVG(Balance) FROM Part GROUP BY RepNum ORDER BY RepNum ; C. SELECT RepNum, COUNT(*), AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ; D. SELECT RepNum, COUNT(*) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
C
Based on the code shown, which query finds the name of the student whose ID is 1167? Student (StudentID, FirstName, LastName, Street, City, State, PostalCode) A. SELECT FirstName, LastName FROM Course WHERE StudentID='1167' ; B. SELECT FirstName, LastName FROM * WHERE StudentID='1167' ; C. SELECT FirstName, LastName FROM Student WHERE StudentID='1167' ; D. SELECT FirstName, LastName FROM Customer WHERE StudentID='1167' ;
C
Based on the code shown, which query lists the complete student table? Student (StudentID, FirstName, LastName, Street, City, State, PostalCode) A. SELECT Student ; B. SELECT & FROM Student ; C. SELECT * FROM Student ; D. SELECT LastName, FirstName, Street, City, State, PostalCode FROM Student
C
Based on the code shown, which query lists the number, name, street, and credit limit of all customers? The records are ordered by customer name within descending credit limit. Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum ) A. SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer SORT BY CreditLimit DESC, CustomerName ; B. SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer SORT BY CreditLimit ASC, CustomerName ; C. SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CreditLimit DESC, CustomerName ; D. SELECT CustomerNum, CustomerName, Street, CreditLimit FROM Customer ORDER BY CreditLimit ASC, CustomerName ;
C
How many lines of output are produced when rows are grouped? A. one line for each group member B. two lines for each group C. one line for each group D. no output is produced
C
What clause do you use in a query to save the results of the query as a table? A. UPDATE B. INSERT C. INTO D. DELETE
C
Based on the code shown, which query changes the postal code of the student with ID 11433 to 14455? Student (StudentID, FirstName, LastName, Street, City, State, PostalCode) A. UPDATE Student SET PostalCode='14455' ; B. UPDATE Student SET PostalCode WHERE StudentID='11433' ; C. UPDATE Student IN PostalCode='14455' WHERE StudentID='11433' ; D. UPDATE Student SET PostalCode='14455' WHERE StudentID='11433' ;
D
Based on the code shown, which query lists the descriptions of all items that are not in Storehouse 3? Item (ItemNum, Description, OnHand, Category, Storehouse, Price ) A. SELECT Description FROM Customer WHERE NOT Storehouse='3' ; B. SELECT Description FROM Item WHERE Storehouse='4' ; C. SELECT Description FROM Item WHERE Storehouse>'3' ; D. SELECT Description FROM Item WHERE NOT Storehouse='3' ;
D
Based on the code shown, which query lists the number, name, and available credit for all customers with credit limits that exceed their balances? Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum ) A. SELECT CustomerNum, CustomerName AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ; B. SELECT CustomerNum, CustomerName, CreditLimit AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ; C. SELECT CustomerNum, CustomerName, Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ; D. SELECT CustomerNum, CustomerName, CreditLimit-Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
D
In Access SQL, which character is used as a wildcard to represent any individual character? A. underscore (_) B. hash tag (#) C. asterisks (*) D. question mark (?)
D
What command do you use to make changes to existing data in a table? A. MODIFY B. CHANGE C. SELECT D. UPDATE
D
Which part of the SQL query specifies the table or tables that contain the data you wish to display in the query results? A. SELECT clause B. WHERE clause C. WITHIN clause D. FROM clause
D
Which query will return the number of rows where the State field is 'AZ' from the table Customers and provide a total of the Payment field? A. SELECT COUNT(*) WHERE State='AZ' SUM(Payment) FROM Customers; B. SELECT FROM Customers COUNT(Payment) SUM(*) WHERE State='AZ'; C. SELECT FROM Customers SUM(Payment) WHERE State='AZ' COUNT(*); D. SELECT COUNT(*), SUM(Payment) FROM Customers WHERE State='AZ';
D
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 (;)
D
You can use the SQL CREATE TABLE command to insert rows into a table. True False
FALSE
A WHERE and a HAVING clause cannot be included in the same query. True False
False
A valid name for a table might be tbl$Student. True False
False
Computed fields are existing fields in the database that you can perform computations with. True False
False
Fields will appear in alphabetical order in the query results of a SELECT clause. True False
False
In a SELECT statement, the WHERE clause is mandatory. True False
False
Instead of listing all the field names in the SELECT clause, you can use the @ symbol. True False
False
Microsoft Access uses the != version of the "not equal to" operator. True False
False
SQL uses an on-screen form to create tables, update tables, and retrieve data from tables. True False
False
The BETWEEN operator is exclusive; it includes all numbers between the lower and higher numbers, but excludes the lower and higher numbers themselves. True False
False
The two tables involved in a join operation must have the same structure. True False
False
There is no difference between the COUNT function and the SUM function. True False
False
When you connect simple conditions using the AND operator, only one of the simple conditions must be true for the compound condition to be true. True False
False
You use the WHERE clause on groups and the HAVING clause on rows. True False
False
A compound condition includes either or both of the AND and OR operators. True False
True
CHAR data types can be used to store numbers that will not be used in calculations. True False
True
Preceding a condition by the NOT operator reverses the result of the original condition. True False
True
When rows are grouped, one line of output is produced for each group. True False
True
You can combine values in character fields using the & operator. True False
True
You can use the GROUP BY clause and the ORDER BY clause in the same SELECT statement. True False
True
