# IFSM 411 Final Exam Rota 2000-10-11

Name: _________________________________
Many of these questions use Microsoft's Northwind database, available in either MS Access or MS SQL 7 Server. You already have an ERD for this database. This is an open book, open notes, open computer exam. No "calls to a friend" are allowed however.
SQL Style. Please don't use the "INNER JOIN ON" MS Access style. Either use SQL1 syntax (list of tables in the FROM clause then the key joins in the WHERE clause), or use SQL2 syntax using NATURAL JOIN where possible (it's frequently possible in this database because the keys fields have the same name). If you do use inner joins with SQL2 syntax, just use the JOIN keyword -- INNER is unnecessary.

1. (15 points) Create new table. There have been a lot of orders over the last 3 years, and consequently the Orders and "Order Details" tables have become very large. You need to create another table (OldOrders) for the old information. This table of old data doesn't need everything that's in the original tables. It only needs the following fields:
   OrderID, CustomerID, EmployeeID, ShippedDate, and Total.
   The first 4 fields are the same as the corresponding fields in the Orders table. The Total field is the total amount of the order (sum of all the OrderDetails UnitPrices times the Quantity -- let's forget the discount). Give the SQL command(s) necessary to create the OldOrders table, including specification of the primary and foreign keys. Use standard SQL datatypes. Define the Total field as DECIMAL (ie, not as MONEY or CURRENCY) with the correct precision and scaling to accommodate numbers to 999,999.00. The foreign keys are all INTEGERs.
   With the foreign key declarations include the appropriate ON UPDATE and ON DELETE clauses with one of them (they should all be the same, but don't bother).
2. (15 points) Copy Data. Now you need to put some data in the old table. Write the SQL command(s) to create records in OldOrders for all orders shipped before January 1, 2000.
3. (15 points) Delete Data. Delete all the old records in the original tables. Tell what kind of ON DELETE action you assuming for the relevant fields of "Order Details".
4. (5 points) Grant permission. Grant permission to the id "boss" to do anything with the new table, even give permission to others.
5. (5 points) Revoke permission. Take away Anne Dodsworth's (id adod) permission to add new records to the Customers table. She is under suspicion of adding fake customers so she shouldn't be permitted to make additions until the investigation is finished.
6. (15 points) Delete the table. Moving the old information to the OldOrders table speeded the searching, but it didn't save any disk space. Now you are running low on disk space, and the company has a freeze on new equipment purchases. You decide you'll have to delete the OldOrders table since that will give you space for at least another year's new orders. Write the SQL command to delete OldOrders. Specify either RESTRICT or CASCADE, and briefly tell why you choose that, and what the specific effect would be of that choice.
7. (10 points) Query. There is a concern that the personnel office has entered some employees more than once in the database so that they have two different records with the only difference being a different primary key (EmployeeID). Write a query to find these problems. List every pair of employees that has the same first name, last name, and birth date.
8. (15 point) Alter. From the previous query you discover that Bob Jones was entered twice, once with EmployeeID 13 and once with id 31. You need to change every 31 to 13 in all tables that use it. Then delete the excess record.
9. (5 points) Query. Which companies (Shippers.CompanyName) ship seafood (Categories.CategoryName)?
10. (5 points) Query. Which employee(s) (Employees.FirstName, Employees.LastName) have sold the most (quantity of) seafood (Categories.CategoryName)? You may create a view if that helps.