Homework # 6


Due: 3/13/2001


Information Resources Management


50 points

Using the same database that was used for HW #5, write SQL for the following. The number of points is indicated.

1. Create the Employee table. (2)

2. Create the OrdLine table. (2)

3. Insert two people into the employee table. One should be the other's supervisor. Explain whether or not the order in which these two inserts are completed is significant. (3)

4. Add yourself to the customer table. (2)

5. Create an order for yourself using the employee (not the supervisor) added in #3. (2)

6. Add two products to the OrdLine table using the order created in #5. (2)

7. Increase the price by 15% for all products provided by "MicroSquishy". (2)

8. Change the address (street, city, state, zip) for the customer added in #4 without using the customer number. (2)

9. Delete the rows added in #4, #5, and #6. Explain whether or not the order in which these three deletes are completed is significant. (3)

10. Reduce by 10% the price of all products that have not been ordered since the beginning of the year, whose quantity on hand (ProdQOH) is greater than 10, and that have a delivery scheduled to ship by the end of March. (2)

11. Create a new table, InternetOrder, that has the same structure of the order table. (2)

12. Add to the InternetOrder table all rows from the order table that were placed on the internet (employee number will be null). (3)

13 Change the InternetOrder table to include an additional column, IP address, and remove employee number from it. (3)

14 Using the new InternetOrder table, calculate commission savings realized because of internet ordering. Assume that average employee commission that would have been paid if an employee had taken the order is 3%. (3)

15. Completely remove the Internet Order table from the database. (2)

Write the queries for the five questions that you did not complete for homework #5.

(3 points each) Keep the numbers from homework #5.