A Guide to SQL, Ninth Edition Solutions 4-1
Chapter 4: Single-Table Queries
Solutions
Answers to Review Questions
1. The basic form of the SELECT command is SELECT-FROM. Specify the columns to be listed
2. Simple conditions are written in the form: column name, comparison operator, column name or
5. Use arithmetic operators and write the computation in place of a column name. You can assign a
6. To check for a value in a character column that is similar to a particular string of characters, use
7. In Oracle, the percent (%) wildcard represents any collection of characters. The underscore (_)
8. Use the IN clause.
10. List the sort keys in order of importance in the ORDER BY clause. The more important key is
11. To sort in descending order, follow the sort key with DESC.
15. Use the GROUP BY clause.
18. [Critical Thinking]
SELECT CUSTOMER_NAME, CITY
Answers to TAL Distributors Exercises
1.
SELECT ITEM_NUM, DESCRIPTION, PRICE
FROM ITEM;
2.
3.
SELECT CUSTOMER_NAME
4.
SELECT ORDER_NUM
FROM ORDERS
In Access, the command would be:
SELECT ORDER_NUM
5.
SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
6.
SELECT ITEM_NUM, DESCRIPTION
A Guide to SQL, Ninth Edition Solutions 4-4
7.
SELECT ITEM_NUM, DESCRIPTION, ON_HAND
or
SELECT ITEM_NUM, DESCRIPTION, ON_HAND
FROM ITEM
WHERE ON_HAND BETWEEN 20 AND 40;
8.
SELECT ITEM_NUM, DESCRIPTION, ON_HAND * PRICE AS ON_HAND_VALUE
9.
10.
11.
In Oracle and SQL Server:
SELECT CUSTOMER_NUM, CUSTOMER_NAME
12.
13.
SELECT *
14.
15.
SELECT SUM(BALANCE)
16.
SELECT ITEM_NUM, DESCRIPTION, ON_HAND
17.
18.
SELECT ITEM_NUM, DESCRIPTION, PRICE
19.
SELECT REP_NUM, SUM(BALANCE)
20.
SELECT REP_NUM, SUM(BALANCE)
21.
SELECT ITEM_NUM
22. [Critical Thinking]
In Oracle and SQL Server
SELECT * FROM ITEM
FROM ITEM;
Answers to Colonial Adventure Tours Exercises
1.
SELECT LAST_NAME
2.
3.
SELECT TRIP_NAME
4.
SELECT TRIP_NAME
5.
6.
SELECT CUSTOMER_NUM, LAST_NAME, FIRST_NAME
7.
8.
SELECT STATE, COUNT(TRIP_ID)
9.
10.
SELECT TYPE, COUNT(TRIP_ID)
11.
A Guide to SQL, Ninth Edition Solutions 4–13
12.
In Oracle and SQL Server:
SELECT TRIP_NAME
13.
In Oracle and SQL Server
SELECT LAST_NAME, FIRST_NAME
14.
SELECT TYPE, AVG(DISTANCE), AVG(MAX_GRP_SIZE)
15.
16.
SELECT RESERVATION_ID
17.
18.
SELECT TRIP_ID, SUM(TRIP_PRICE)
19.
20. [Critical Thinking]
In Oracle and SQL two alternates are
SELECT RESERVATION_ID, TRIP_ID
FROM RESERVATION
WHERE TRIP_DATE BETWEEN ‘7/1/2016‘ AND ‘7/31/2016’;
Or
Or
SELECT RESERVATION_ID, TRIP_ID
FROM RESERVATION
WHERE TRIP_DATE >= #7/1/2016# AND TRIP_DATE <= #7/31/2016#;
Note: You also can search for >6/30/2016 and < 8/1/2016 in Oracle, SQL Server, and Access.
A Guide to SQL, Ninth Edition Solutions 4–16
Answers to Solmaris Condominium Group Exercises
1.
2.
3.
4.
SELECT LAST_NAME, FIRST_NAME
FROM OWNER
WHERE CITY <> ‘Bowton‘;
5.
6.
7.
SELECT UNIT_NUM
8.
9.
SELECT UNIT_NUM
10.
11.
SELECT OWNER_NUM, LAST_NAME
12.
13.
SELECT COUNT(CONDO_ID)
14.
15. [Critical Thinking]
SELECT OWNER_NUM, LAST_NAME
FROM OWNER
WHERE STATE IN (‘FL’, ‘GA’, ‘SC‘);
Or
16. [Critical Thinking]
In Oracle and SQL Server
SELECT *