Assignment p

Instructions : Add your SQL command and results (using copy/paste and screen capture) to each question. Do NOT remove any existing content or images from this file. You MUST include both your SQL Command and Results from running that command in your response to each question.

Take advantage of the “Hints” that accompany each question.

1. Create a query named MAINE_TRIPS. It consists of the trip ID, trip name, start location,

distance, maximum group size, type, and season for every trip located in Maine (ME). Mod1218

a. (4 pts) Write and execute the query that lists the trip ID, trip name, start location,distance, maximum group size, type, and season for every trip located in Maine (ME). This is a simple SELECT statement using one table. After your SELECT statement returns correct results save your query as MAINE_TRIPS.

Put your SQL command and results here:

b. (5 pts) Using the MAINE_TRIPS query write and execute the command to retrieve the trip ID, trip name, and distance for every Biking trip.

Put your SQL command and results here:

c. (5 pts) Write and execute the query that the MSAccess DBMS actually executes.

Put your SQL command and results here:

2. Create a query named RESERVATION_CUSTOMER that consists of the reservation ID, trip ID, trip date, customer number, customer last name, customer first name, and phone number.

a. (4 pts) Write and execute the SELECT command to create the RESERVATION_CUSTOMER query. This is a multiple table query involving the RESERVATION table and the CUSTOMER table. After your SELECT statement returns correct results save your query as RESERVATION_CUSTOMER.

Put your SQL command and results here:

b. (5 pts) Using the RESERVATION_CUSTOMER query write and execute the command to retrieve the reservation ID, trip ID, trip date, and customer last name for every with a trip date of September 11, 2016.

Put your SQL command and results here:

c. (5 pts) Write and execute the query that the MSAccess DBMS actually executes.

Put your SQL command and results here:

3. COMPLETE, but do not execute, the commands to grant the following privileges. The section “Security” in your eBook (Chapter 7, page 204) contains a number of related examples on the GRANT command.

a. (4 pts) User Rodriquez must be able to retrieve data from the TRIP table. Complete the following SQL command:

GRANT SELECT ON ???? TO ????;

b. (4 pts) Users Gomez and Liston must be able to add new reservations and customers to the database. Complete the following two SQL commands:

GRANT INSERT ON ???? TO ????, ????;

GRANT INSERT ON ???? TO ????, ????;

c. (4 pts) Users Andrews and Zimmer must be able to change the price of any trip. Complete the following SQL command:

GRANT UPDATE (????) ON ???? TO ????, ????;

d. (4 pts) All users must be able to retrieve the trip name, start location, distance and type for every trip. Complete the following SQL command:

GRANT SELECT (????, ????, ????, ????) ON ????

TO ????;

e. (4 pts) User Golden must be able to add and delete guides. Complete the following two SQL commands:

GRANT INSERT ON ???? TO ????;

GRANT DELETE ON ???? TO ????;

f. (4 pts) User Andrews must be able to create an index for the TRIP table. Complete the following SQL command:

GRANT INDEX ON ???? TO ????;

g. (4 pts) Users Andrews and Golden must be able to change the structure of the CUSTOMER table. Complete the following SQL command:

GRANT ALTER ON ???? TO ????, ????;

h. (4 pts) User Golden must have all privileges on the TRIP, GUIDE, and TRIP_GUIDES tables. Complete the following three SQL commands:

GRANT ALL ON ???? TO ????;

GRANT ALL ON ???? TO ????;

GRANT ALL ON ???? TO ????;

4. (3 pts) COMPLETE, but do not execute, the command to revoke all privileges from user Andrews. Complete the following SQL command:

REVOKE ALL PRIVILEGES FROM ????;

5. COMPLETE, but do not execute, the commands to create the following indexes. The section “Security” in your eBook (Chapter 7, page 209) contains a number of related examples on the INDEX command.

a. (3 pts) Create an index named TRIP_INDEX1 on the TRIP_NAME column in the TRIP

table. Complete the following SQL command:

CREATE INDEX ???? ON TRIP(????);

b. (3 pts) Create an index named TRIP_INDEX2 on the TYPE column in the TRIP table. Complete the following SQL command:

CREATE INDEX ???? ON TRIP(????);

c. (3 pts) Create an index named TRIP_INDEX3 on the LAST_NAME and FIRST_NAME

columns in the CUSTOMER table. Complete the following SQL command:

CREATE INDEX ???? ON ????(????, ????);

6. (3 pts) COMPLETE, but do not execute, the commands to delete the index named BOOK_INDEX3. Complete the following SQL command:

DROP INDEX ????;

7. COMPLETE, but do not execute, the commands to obtain the following information from the system catalog. The section “System Catalog” in your eBook (Chapter 7, page 212) contains a number of related examples on the SQL commands that obtain information from the system catalog. Please note that all SQL examples in the this section of the eBook obtain information from “DBA” tables. In all of the following questions “DBA” has been changed to “ALL” in order to allow your SQL commands to execute properly.

a. (3 pts) List every column (i.e., COLUMN_NAME) in the GUIDE table and its associated data type (i.e., DATA_TYPE)

.

Put your SQL command here: Do NOT run this SQL command.

b. (3 pts) List every table (i.e. TABLE_NAME) that contains a column (i.e., COLUMN_NAME) that is named TRIP_ID.

Put your SQL command here: Do NOT run this SQL command.

c. (3 pts) List the table name, column name, and data type for the columns named TRIP_ID and TRIP_NAME. Order the results by table name within column name. (That is, column name is the major sort key and table name is the minor sort key.)

Put your SQL command here: Do NOT run this SQL command.

8. (3 pts) COMPLETE, but do not execute, the following ALTER command that adds the CUSTOMER_NUM column as a foreign key in the RESERVATION table. Complete the following SQL command:

ALTER TABLE RESERVATION

ADD FOREIGN KEY (CUSTOMER_NUM) REFERENCES CUSTOMER(????????_???);

9. (3 pts) COMPLETE, but do not execute, the following ALTER command that ensures that the TYPE column in the TRIP table can accept only values of Biking, Hiking or Paddling. Complete the following SQL command:

ALTER TABLE ????

ADD CHECK (TYPE IN ('??????', '??????', '????????'));