TAL DISTRIBUTORS

From: FULL A Guide to SQL, Ninth Edition by Philip J. Pratt, Mary Z. Last

Due TONIGHT AT 12midnight NY TIME (roughly 5 hours)

Pages 221 -- 222

Questions -- 1 -- 10

1. Create a view named MAJOR_CUSTOMER. It consists of the customer number, name, balance, credit limit, and rep number for every customer whose credit limit is $10,000 or less. a. Write and execute the CREATE VIEW command to create the MAJOR_CUSTOMER view. b. Write and execute the command to retrieve the customer number and name of each customer in the MAJOR_CUSTOMER view with a balance that exceeds the credit limit. c. Write and execute the query that the DBMS actually executes. d. Does updating the database through this view create any problems? If so, what are they? If not, why not? 2. Create a view named ITEM_ORDER. It consists of the item number, description, price, order number, order date, number ordered, and quoted price for all order lines currently on file. a. Write and execute the CREATE VIEW command to create the ITEM_ORDER view. b. Write and execute the command to retrieve the item number, description, order number, and quoted price for all orders in the ITEM_ORDER view for items with quoted prices that exceed $100. c. Write and execute the query that the DBMS actually executes. d. Does updating the database through this view create any problems? If so, what are they? If not, why not? 3. Create a view named ORDER_TOTAL. It consists of the order number and order total for each order currently on file. (The order total is the sum of the number of units ordered multiplied by the quoted price on each order line for each order.) Sort the rows by order number. Use TOTAL_AMOUNT as the name for the order total. a. Write and execute the CREATE VIEW command to create the ORDER_TOTAL view. b. Write and execute the command to retrieve the order number and order total for only those orders totaling more than $500. c. Write and execute the query that the DBMS actually executes. d. Does updating the database through this view create any problems? If so, what are they? If not, why not? 4. Write, but do not execute, the commands to grant the following privileges: a. User Ashton must be able to retrieve data from the ITEM table. b. Users Kelly and Morgan must be able to add new orders and order lines. c. User James must be able to change the price for all items. d. User Danielson must be able to delete customers. e. All users must be able to retrieve each customer's number, name, street, city, state, and postal code. f. User Perez must be able to create an index on the ORDERS table. g. User Washington must be able to change the structure of the ITEM table. h. User Grinstead must have all privileges on the ORDERS table. 5. Write, but do not execute, the command to revoke the privilege given to user Ashton in Exercise 4a. 6. Perform the following tasks: a. Create an index named ITEM_INDEX1 on the ITEM_NUM column in the ORDER_LINE table. b. Create an index named ITEM_INDEX2 on the CATEGORY column in the ITEM table. c. Create an index named ITEM_INDEX3 on the CATEGORY and STOREHOUSE columns in the ITEM table. d. Create an index named ITEM_INDEX4 on the CATEGORY and STOREHOUSE columns in the ITEM table. List categories in descending order. 7. Delete the index named ITEM_INDEX3. 8. Write the commands to obtain the following information from the system catalog. Do not execute these commands unless your instructor asks you to do so. a. List every table that you own. b. List every column in the ITEM table and its associated data type