# 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).
   Answer:

       CREATE OldOrders (
          OrderID     INTEGER PRIMARY KEY,
          CustomerID  INTEGER REFERENCES Customers
                              ON UPDATE CASCADE
                              ON DELETE SET NULL,
          EmployeeID  INTEGER REFERENCES Employees
                              ON UPDATE CASCADE
                              ON DELETE SET NULL,
          ShippedDate DATE,
          Total       DECIMAL(8,2),

   The foreign keys are declared to get the new key value if the primary key in the Customers/Employees tables change (ON UPDATE CASCADE). However, someone wants to delete one of those original records, perhaps it would be best to not let this partial archival data stop them. That was the reasoning behind ON DELETE SET NULL. However, many possible answers were accepted here. DATE could also be TIMESTAMP or DATETIME, with or without explicit lengths. DECIMAL(8,2) is the minimum size that can accommodate numbers to 999,999.99.
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.
   Answer:

       INSERT INTO OldOrders
              SELECT Orders.OrderID, CustomerID, EmployeeID, ShippedDate,
                     SUM(UnitPrice * Quantity)
                FROM Orders NATURAL JOIN "Order Details"
                WHERE ShippedDate < '2000-01-01'
                GROUP BY Orders.OrderID, CustomerID, EmployeeID, ShippedDate;

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".
   Answer: This question was thrown out because of its vagueness. I wanted the DELETE below for sure (tho many just deleted all the records from Orders), and wanted either a DELETE (which should have preceded this) for the "Order Details" table, or some explanation of how that might occur (cascading deletes). A number of people put joins in their DELETES - not legal unless it's in a subselect.

       DELETE FROM Orders
         WHERE ShippedDate < '2000-01-01';

4. (5 points) Grant permission. Grant permission to the id "boss" to do anything with the new table, even give permission to others.
   Answer:

       GRANT ALL PRIVILEGES
          ON OldOrders
          TO boss
          WITH GRANT OPTION;

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.
   Answer:

       REVOKE INSERT
           ON CUSTOMERS
           FROM adod;

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.
   Answer:

       DROP TABLE OldOrders ...;

   I accepted either RESTRICT or CASCADE or neither in place of the ... . The original plan was to grade this based on how good a reason was given for either of these (and there could be good reasons for either RESTRICT or CASCADE). But we didn't cover these issues well enough in class for this to be worth the points. So I didn't grade the explanations, which varied from excellent to non-existent.
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.
   Answer:

       -- A self=join is needed when comparing records in the same table.
       SELECT T1.FirstName, T1.LastName, T1.BirthDate
         FROM Employees AS T1, Employees AS T2
         WHERE T1.FirstName = T2.FirstName
           AND T1.LastName  = T2.LastName
           AND T1.BirthDate = T2.BirthDate
           AND T1.EmployeeID <> T2.EmployeeID;

   Many forgot that last inequality.
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.
   Answer:

       -- Orders is the only table with an EmployeeID foreign key.
       -- First, change all the fields in the Orders table that are wrong
       UPDATE Orders
          SET EmployeeID = 13
          WHERE EmployeeID = 31;

       -- Now that no foreign keys reference the '13' record in Employees, delete it.
       DELETE FROM Employees
         WHERE EmployeeID = 31;

9. (5 points) Query. Which companies (Shippers.CompanyName) ship seafood (Categories.CategoryName)?
   Answer:

       SELECT DISTINCT CompanyName
         FROM (Shippers JOIN Orders ON ShipperID = ShipVia)
              NATURAL JOIN "Order Details"
              NATURAL JOIN Products
              NATURAL JOIN Categories
         WHERE CategoryName = 'Seafood';

   The DISTINCT (which might have to be DISTINCTROW in some systems) can also be accomplished with GROUP BY. It was interesting to see how differently some people thought about this problem. Here is a solution that several chose:

       SELECT CompanyName
         FROM Shippers
         WHERE ShipperID IN
               (SELECT ShipVia
                  FROM Orders
                  WHERE OrderID IN
                        (SELECT OrderID
                           FROM "Order Details"
                           WHERE ProductID IN
                                 (SELECT ProductID
                                    FROM Products
                                    WHERE CategoryID IN
                                          (SELECT CategoryID
                                             FROM Categories
                                             WHERE (CategoryName = 'Seafood'
                                          )
                                 )
                        )
                );

   There were other variations.
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.
    Answer:

        -- Create a VIEW which as the sum of the quantities sold by each employee.
        CREATE VIEW EmpQuantity (FName, LName, TotalQuantity)
               AS (SELECT FirstName, LastName, SUM(Quantity)
                     FROM Employees NATURAL JOIN ORDERS
                                    NATURAL JOIN "Order Details"
                                    NATURAL JOIN Products
                                    NATURAL JOIN Categories
                     WHERE CategoryName = 'Seafood'
                     GROUP BY FirstName, LastName;

        -- Use this select on the view to find everyone who sold the max
        SELECT FName, LName
          FROM EmpQuantity
          WHERE TotalQuantity = (SELECT MAX(TotalQuantity)
                                   FROM EmpQuantity);