Chapter 3: The Relational Model 2: SQL
True / False
1. SQL uses commands to create tables, update tables, and retrieve data from tables.
a.
True
b.
False
True
1
71
2. You can use the SQL CREATE TABLE command to insert rows into a table.
a.
True
b.
False
3. A valid name for a table might be tbl$Student.
a.
True
b.
False
4. CHAR data types are numbers without a decimal part.
a.
True
b.
False
5. Fields will appear in the query results in the order in which they are listed in the SELECT clause.
a.
True
b.
False
True
1
74
6. In a SELECT statement, the WHERE clause is mandatory.
a.
True
b.
False
7. Instead of listing all the field names in the SELECT clause, you can use the @ symbol.
Chapter 3: The Relational Model 2: SQL
a.
True
b.
False
8. A simple condition includes the field name, a comparison operator, and either another field name or a value.
a.
True
b.
False
True
1
77
9. There are two versions of the “not equal to” operator: <> and !=.
a.
True
b.
False
True
1
77
10. When you connect simple conditions using the AND operator, all the simple conditions must be false for the
compound condition to be true.
a.
True
b.
False
11. Preceding a condition by the NOT operator reverses the truth or falsity of the original condition.
a.
True
b.
False
True
1
80
12. The BETWEEN operator is an essential feature of SQL.
a.
True
b.
False
13. You can combine values in character fields.
a.
True
b.
False
Chapter 3: The Relational Model 2: SQL
True
1
84
14. The IN operator provides a concise way of phrasing certain conditions.
a.
True
b.
False
True
1
86
15. There is no difference between the COUNT function and the SUM function.
a.
True
b.
False
16. You can use the GROUP BY clause and the ORDER BY clause in the same SELECT statement.
a.
True
b.
False
True
1
93
17. The HAVING clause is to groups what the WHERE clause is to rows.
a.
True
b.
False
True
1
94
18. When rows are grouped, one line of output is produced for each group.
a.
True
b.
False
True
1
93
19. A WHERE and a HAVING clause cannot be included in the same query.
a.
True
b.
False
Chapter 3: The Relational Model 2: SQL
20. For each pair of tables to be joined, a condition must be included indicating how the tables are related.
a.
True
b.
False
True
1
99
21. When you use a name containing a space in Access SQL, you must ____.
a.
enclose it in quotation marks
b.
enclose it in square brackets
c.
enclose it in asterisks
d.
enclose it in question marks
b
1
72
22. Many versions of SQL require you to end a command with a ____.
a.
comma (,)
b.
period (.)
c.
colon (:)
d.
semicolon (;)
d
1
73
23. The basic form of an SQL retrieval command is ____.
a.
SELECT-WHERE-FROM
b.
CREATE-SELECT-FROM
c.
SELECT-WHERE
d.
SELECT-FROM-WHERE
d
1
74
24. When used after the word SELECT, the ____ 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.
ampersand
c.
#
d.
$
1
25. To use a wildcard, include the ____ operator in the WHERE clause.
a.
LIKE
b.
AS
c.
BETWEEN
d.
UNION
1
85
26. In versions of SQL other than Access, the ____ is used as a wildcard to represent any collection of characters.
a.
asterisks (*)
b.
percent sign (%)
c.
underscore (_)
d.
question mark (?)
b
1
85
27. In Access SQL, the ____ is used as a wildcard to represent any individual character.
a.
asterisks (*)
b.
percent sign (%)
c.
underscore (_)
d.
question mark (?)
d
1
86
28. The ____ function calculates the number of entries in a table.
a.
COUNT
b.
SUM
c.
MAX
d.
MIN
1
89
29. When a subquery is used, ____ is(are) evaluated first.
a.
the subquery query
b.
the outer query
c.
both a and b simultaneously
d.
whichever query is selected to execute first by the user
1
75
30. The ____ clause can be used to create groups of records.
a.
AGGREGATE
b.
SORT BY
c.
ORDER BY
d.
GROUP BY
d
1
93
31. When rows are grouped, ____.
a.
the totals appear with the rows
b.
the rows appear in order
c.
one line of output is produced for each group
d.
no output is produced
1
93
32. To make changes to existing data in a table, you would use the ____ command.
a.
MODIFY
b.
CHANGE
c.
SELECT
d.
UPDATE
d
1
100
33. To add new data to a table, use the ____ command.
a.
INSERT
b.
APPEND
c.
ADDTO
d.
SELECT
1
100
34. You can save the results of a query as a table by including the ____ clause in the query.
a.
UPDATE
b.
INSERT
c.
INTO
d.
DELETE
1
92
35. Based on the code above, list the number, name, credit limit, and balance for all customers with credit limits that
exceed their balances.
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 ;
36. Based on the code above, list 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.
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 ;
37. Based on the code above, list the number, name, and available credit for all customers with credit limits that exceed
their balances.
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 ;
38. Based on the code above, list the number, name, and complete address of every customer located on a street that
contains the letters “Oxford”.
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
Chapter 3: The Relational Model 2: SQL
LIKE “Oxford” ;
39. Based on the code above, list the number, name, street, and credit limit of all customers. Order the customers by name
within descending credit limit.
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 ;
40. Based on the code above, for each sales rep, list the rep number, the number of customers assigned to the rep, and the
average balance of the rep’s customers. Group the records by rep number and order the records by rep number.
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 ;
41. Based on the code above, list the number and name of all customers that are either represented by sales rep 30 or that
currently have orders on file, or both.
a.
SELECT CustomerNum, CustomerName, FROM Customer WHERE RepNum=’30’ UNION SELECT
Customer.CustomerNum, CustomerName, FROM Customer ;
b.
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 ;
42. Based on the code above, list the descriptions of all items that are located in Storehouse3 and for which there are more
than 20 units on hand.
Chapter 3: The Relational Model 2: SQL
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 ;
43. Based on the code above, list the descriptions of all items that are located in Storehouse 3 or for which there are more
than 20 units on hand, or both.
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 ;
44. Based on the code above, list the descriptions of all items that are not in Storehouse 3.Based on the code above, list the
descriptions of all items that are not in Storehouse 3.
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’ ;
45. Based on the code above, find how many items are in category TOY.
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’ ;
46. Based on the code above, list the complete student table.
a.
SELECT Student ;
b.
SELECT “ampersand” FROM Student ;
c.
SELECT * FROM Student ;
d.
SELECT LastName, FirstName, Street, City, State, PostalCode FROM Student
Chapter 3: The Relational Model 2: SQL
47. Based on the code above, list the name of every student whose postal code is 10113.
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’ ;
48. Based on the code above, find the name of the student whose ID is 1167.
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’ ;
49. Based on the code above, change the postal code of the student with ID 11433 to 14455.
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’ ;
50. Based on the code above, delete any row in the OrderLine table in which the item number is MT03.
a.
DELETE FROM OrderLine WHERE ItemNum=’MT03’ ;
b.
DELETE FROM OrderLine WHERE ItemNum=MT03;
c.
DELETE FROM OrderLine WHERE ItemNum=’MT03’
d.
DELETE FROM OrderLine WHERE ItemNum=’MT3’ ;
Completion
51. SQL was developed under the name ____________________ at the IBM San Jose research facilities.
52. One common restriction placed on table and column names by DBMSs is that names cannot exceed
____________________ characters.
Chapter 3: The Relational Model 2: SQL
53. One common restriction placed on table and column names by DBMSs is that names must start with a(n)
____________________.
54. You use the SQL ____________________ command to create a table by describing its layout.
55. The ____________________ data type stores integers, but uses less space than INTEGER.
56. Use the ____________________ data type for fields that contain letters and other special characters, and for
fields that contain numbers that will not be used for arithmetic.
57. Words that are part of the SQL language are called ____________________.
58. When you connect simple conditions using the ____________________ operator, the compound condition will be true
whenever any of the simple conditions are true.
59. By using the word ____________________ in a query after a computation, you can assign a name to the
computed field.
60. A field whose values you derive from existing fields is called a(n) ____________________ field.
Chapter 3: The Relational Model 2: SQL
61. In Access, you use the ____________________ operator to concatenate fields.
62. Many versions of SQL other than Access use the ____________________ as a wildcard to represent any
individual character.
63. In SQL, you sort data using the ____________________ clause.
64. When you need to sort data on two fields, the more important sort key is called the ____________________ sort key.
65. When you need to sort data on two fields, the less important key is called the ____________________ sort
key.
66. SQL has built-in functions, which are also called ____________________ functions.
67. ____________________ means creating groups of records that share some common characteristics.
68. To qualify a field name, precede the name of the field with the name of the table, followed by a(n)
____________________.
69. The ____________________ of two tables is a table containing all rows that are in either the first table, the
second table, or both.
70. The two tables involved in a union must have the same structure, or be ____________________.
71. What are some common restrictions placed on table and column names by DBMSs?
72. Describe five data types that you will often encounter when working with databases.
72 | 73
73. How are compound conditions formed?
74. Describe how to construct a detailed query in a step-by-step fashion.
Chapter 3: The Relational Model 2: SQL
75. Discuss the restriction about the structure of two tables involved in a union.