A Guide to SQL, Ninth Edition Solutions 7-1
Chapter 7: Database Administration
Solutions
Answers to Review Questions
2. CREATE VIEW.
4. The DBMS merges the query entered by the user with the defining query, producing the query
that SQL actually executes.
6. Views involving statistics and joins on non-primary key columns.
10. To make data retrieval more efficient.
11. Use the CREATE INDEX command to create an index. Use the CREATE UNIQUE INDEX
14. The DBMS maintains information about the tables, columns, indexes, and other system
elements in the system catalog (catalog) or data dictionary. Information about tables is kept
15. Inserting a record into the CUSTOMER table where the REP_NUM did not match a
A Guide to SQL, Ninth Edition Solutions 7-2
18. Integrity constraints are rules that the data in the database must follow to ensure that only
20. Primary key constraints are usually specified when you create a table. You can specify them
22. [Critical Thinking] Answers will vary. Answers should note that referential integrity is the
rule that if a table A contains a foreign key that matches the primary key of table B, then the
23. [Critical Thinking] Answers will vary. Answers should note that a data dictionary is a catalog
that stores data about the entities, attributes, relationships, programs, and other objects in a
Answers to TAL Distributors Exercises
1 a.
b.
SELECT CUSTOMER_NUM, CUSTOMER_NAME
c.
SELECT CUSTOMER_NUM, CUSTOMER_NAME
A Guide to SQL, Ninth Edition Solutions 7-3
d.
2 a.
CREATE VIEW ITEM_ORDER AS
SELECT ITEM.ITEM_NUM, DESCRIPTION, PRICE, ORDER_LINE.ORDER_NUM,
b.
SELECT ITEM_NUM, DESCRIPTION, ORDER_NUM, QUOTED_PRICE
FROM ITEM_ORDER
WHERE QUOTED_PRICE > 100;
c.
SELECT ITEM.ITEM_NUM, DESCRIPTION, ORDER_LINE.ORDER_NUM,
QUOTED_PRICE
d.
3. a.
CREATE VIEW ORDER_TOTAL (ORDER_NUM, TOTAL_AMOUNT) AS
A Guide to SQL, Ninth Edition Solutions 7-4
b.
SELECT ORDER_NUM, TOTAL_AMOUNT
c.
SELECT ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
4. a. GRANT SELECT ON ITEM TO ASHTON;
b. GRANT INSERT ON ORDERS TO KELLY, MORGAN;
GRANT INSERT ON ORDER_LINE TO KELLY, MORGAN;
6. a.
A Guide to SQL, Ninth Edition Solutions 7-5
7.
8. a.
SELECT TABLE_NAME
b.
SELECT COLUMN_NAME, DATA_TYPE
9.
ALTER TABLE ORDER_LINE
11. [Critical Thinking] Yes, Toys Galore would still be included because the increase is still within the criteria
Answers to Colonial Adventure Tours Exercises
1. a.
CREATE VIEW MAINE_TRIPS AS
b.
A Guide to SQL, Ninth Edition Solutions 7-6
SELECT TRIP_ID, TRIP_NAME, DISTANCE
c.
SELECT TRIP_ID, TRIP_NAME, DISTANCE
d.
2. a.
CREATE VIEW RESERVATION_CUSTOMER AS
b.
SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE, LAST_NAME
In Access, the command would be:
A Guide to SQL, Ninth Edition Solutions 7-7
c.
SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE, LAST_NAME
FROM RESERVATION, CUSTOMER
In Access, the command would be:
SELECT RESERVATION_ID, TRIP_ID, TRIP_DATE, LAST_NAME
FROM RESERVATION, CUSTOMER
3. a.
CREATE VIEW TRIP_INVENTORY AS
SELECT STATE, COUNT(*) AS UNITS
FROM TRIP
b.
c.
SELECT STATE, COUNT(*) AS UNITS
FROM TRIP
GROUP BY STATE
4. a. GRANT SELECT ON TRIP TO RODRIGUEZ;
b. GRANT INSERT ON RESERVATION TO GOMEZ, LISTON;
GRANT INSERT ON CUSTOMER TO GOMEZ, LISTON;
c. GRANT UPDATE (PRICE) ON RESERVATION TO ANDREWS, ZIMMER;
6. a.
b.
c.
7.
8. a.
SELECT COLUMN_NAME, DATA_TYPE
b.
SELECT TABLE_NAME
c.
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
A Guide to SQL, Ninth Edition Solutions 7-9
OR COLUMN_NAME = ‘TRIP_NAME’
9.
10.
11. [Critical Thinking]
Answers to Solmaris Condominium Group Exercises
1. a.
CREATE VIEW SMALL_CONDO AS
b.
c.
SELECT LOCATION_NUM, UNIT_NUM, CONDO_FEE
d.
2. a.
CREATE VIEW CONDO_OWNERS AS
b.
SELECT LOCATION_NUM, UNIT_NUM, CONDO_FEE, SQR_FT, LAST_NAME
c.
SELECT LOCATION_NUM, UNIT_NUM, CONDO_FEE, SQR_FT, LAST_NAME
d.
3. a.
CREATE VIEW CONDO_FEES (SQR_FT, AVERAGE_FEE) AS
b.
A Guide to SQL, Ninth Edition Solutions 7–11
c.
SELECT SQR_FT, AVG(CONDO_FEE) AS AVERAGE_FEE
FROM CONDO_UNIT
d.
Because this view involves statistics, you cannot update the database via this view.
4. a. GRANT SELECT ON CONDO_UNIT TO OLIVER;
b. GRANT INSERT ON OWNER TO CRANDALL, PEREZ;
GRANT INSERT ON CONDO_UNIT TO CRANDALL, PEREZ;
6. a.
7.
8. a.
A Guide to SQL, Ninth Edition Solutions 7–12
b.
SELECT TABLE_NAME
FROM DBA_TAB_COLUMNS
9.
ALTER TABLE CONDO_UNIT
ADD FOREIGN KEY(OWNER_NUM) REFERENCES OWNER;
10.
11. [Critical Thinking] Foreign keys for the Solmaris Condominium Group database are:
Table: CONDO_UNIT
LOCATION_NUM
ALTER TABLE CONDO_UNIT
ADD FOREIGN KEY(LOCATION_NUM) REFERENCES LOCATION;
Or
ALTER TABLE CONDO_UNIT
ADD FOREIGN KEY(LOCATION_NUM) REFERENCES LOCATION(LOCATION_NUM);