A Guide to SQL, Ninth Edition Solutions 5-1
Chapter 5: Multiple-Table Queries
Solutions
Answers to Review Questions
1. Indicate in the SELECT clause all columns to display, list in the FROM clause all
2. You must qualify names if the same name appears in more than one of the tables listed
3. IN and EXISTS.
5. An alias is an alternate name for a table. To specify one in SQL, follow the name of the
6. List the table twice in the FROM clause, using two different aliases. Use these aliases
7. Use the UNION, INTERSECT, and MINUS operators to create a union, intersection,
8. Two tables are union-compatible if they have the same number of columns and if their
corresponding columns have identical data types and lengths.
10. If a subquery is preceded by the ANY operator, the condition is true if it is satisfied
by any value (one or more) produced by the subquery.
12. In a left outer join, all rows from the left table (that is, the first table listed) are
13. In a right outer join, all rows from the table on the right will be included regardless of
whether they match rows from the table on the left. Rows from the table on the left
14. The formal name is Cartesian product. To form a product of two tables, include both
tables in the FROM clause and omit the WHERE clause.
15. [Critical Thinking] Answers will vary. Answers should note that an equi-join is
16. [Critical Thinking] Answers will vary. Answers should mention that cost-based query
optimizers assign an estimated “cost” to each possible query execution plan, and
choose the execution plan with the smallest cost. Queries that join three or more tables
benefit from a cost-based query optimizer.
Answers to TAL Distributors Exercises
1.
2.
In Oracle and SQL Server, the command is:
In Access, the command is:
SELECT ORDER_NUM, ORDERS.CUSTOMER_NUM, CUSTOMER_NAME
3.
4.
In Oracle and SQL Server, the command is:
SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
In Access, the command is:
SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
WHERE CUSTOMER_NUM IN
5.
In Oracle and SQL Server, the command is:
SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
In Access, the command is:
A Guide to SQL, Ninth Edition Solutions 5-4
SELECT CUSTOMER_NUM, CUSTOMER_NAME
6.
In Oracle and SQL Server, the command is:
SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
In Access, the command is:
SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
7.
SELECT ORDERS.ORDER_NUM, ORDER_DATE, ITEM.ITEM_NUM, DESCRIPTION, CATEGORY
8.
SELECT ORDERS.ORDER_NUM, ORDER_DATE, ITEM.ITEM_NUM, DESCRIPTION, CATEGORY
9.
SELECT REP_NUM, LAST_NAME, FIRST_NAME
10.
SELECT DISTINCT REP.REP_NUM, LAST_NAME, FIRST_NAME
11.
SELECT CUSTOMER.CUSTOMER_NUM, CUSTOMER_NAME
12.
SELECT F.ITEM_NUM, F.DESCRIPTION, S.ITEM_NUM, S.DESCRIPTION, F.CATEGORY
13.
SELECT ORDERS.ORDER_NUM, ORDER_DATE
14.
A Guide to SQL, Ninth Edition Solutions 5-8
15.
SELECT ORDER_NUM, ORDER_DATE
FROM CUSTOMER, ORDERS
16.
SELECT ORDER_NUM, ORDER_DATE
FROM CUSTOMER, ORDERS
17.
SELECT ORDER_NUM, ORDER_DATE
FROM CUSTOMER, ORDERS
18.
SELECT ITEM_NUM, DESCRIPTION, PRICE, CATEGORY
19.
SELECT ITEM.ITEM_NUM, DESCRIPTION, ON_HAND, NUM_ORDERED
20. [Critical Thinking]
SELECT ITEM_NUM, DESCRIPTION, PRICE, CATEGORY
FROM ITEM
This query answers the question “Which items have a price greater than any price in the category GME?”
21. [Critical Thinking]
SELECT CUSTOMER_NUM, CUSTOMER_NAME, LAST_NAME, FIRST_NAME
FROM CUSTOMER
RIGHT JOIN REP
A Guide to SQL, Ninth Edition Solutions 5–10
Answers to Colonial Adventure Tours Exercises
1.
SELECT RESERVATION_ID, TRIP_ID, RESERVATION.CUSTOMER_NUM, LAST_NAME
2.
SELECT RESERVATION_ID, TRIP_ID, NUM_PERSONS
3.
SELECT TRIP_NAME
4.
SELECT TRIP_NAME
5.
In Oracle and SQL Server, the command is:
SELECT LAST_NAME, TRIP_NAME, START_LOCATION
In Access, the command is:
AND TRIP_DATE = #7/23/2016#;
6.
SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE
7.