SAMPLE SOLUTION

Assignment 3
CIS 451/551 Fall 2007


On the stores7 database

1.  Find all customers who have spent at least $10 on items made by the company 'Anza'.

SELECT c.fname, c.lname, SUM(i.total_price) AS totAnza
FROM customer c NATURAL JOIN orders o NATURAL JOIN items i NATURAL JOIN manufact m
WHERE m.manu_name="Anza"
GROUP BY c.customer_num
HAVING totAnza>=10;

2.  For each customer, add up the total amount spent purchasing items made by the company 'Anza'.  Include those customers who have bought nothing (with $0 next to their name).

SELECT c.fname, c.lname, IFNULL(SUM(a.total_price),0) AS totAnza
FROM customer c NATURAL LEFT JOIN
    (SELECT o.customer_num, i.total_price
     FROM orders o NATURAL JOIN items i NATURAL JOIN manufact m
     WHERE m.manu_name="Anza") a
GROUP BY c.customer_num
ORDER BY 2,1;

3.  For each customer, add up the total amount spent by that customer.  Include the shipping charge.  You do not need to include those who have ordered nothing.

SELECT c.fname, c.lname, SUM(t.tot + o.ship_charge) totalSpent
FROM customer c NATURAL JOIN orders o NATURAL JOIN
     (SELECT i.order_num, SUM(i.total_price) AS tot
      FROM items i 
      GROUP BY 1) AS t
GROUP BY c.customer_num
ORDER BY 2, 1;

4.   For each manufacturer, list the names of those customers who have purchased everything made by that manufacturer.  (Note: you'll only get 'Norge', with two customers.)

SELECT m.manu_name, c.fname, c.lname
FROM customer c, manufact m
WHERE NOT EXISTS
        (SELECT s.stock_num, s.manu_code
        FROM stock s
        WHERE s.manu_code=m.manu_code
                AND s.stock_num NOT IN
                (SELECT i.stock_num
                 FROM orders o, items i
                 WHERE c.customer_num=o.customer_num
                        AND o.order_num=i.order_num
                        AND i.manu_code=m.manu_code)
          );



On the company database


5.  For each project, find the total and average of the working hours allocated to that project.

SELECT p.pname, SUM(w.hours) totHours, ROUND(AVG(w.hours),1) avgHours
FROM project p JOIN works_on w ON p.pnumber=w.pno
GROUP BY 1;

6.  Determine which project has the maximum number of allocated hours.  Only return one project name
(more than one OK if there are ties).

SELECT p.pname, SUM(w.hours) totHours
FROM project p JOIN works_on w ON p.pnumber=w.pno
GROUP BY 1
HAVING totHours >= ALL
        (SELECT SUM(w.hours) totHours
         FROM project p JOIN works_on w ON p.pnumber=w.pno
         GROUP BY p.pnumber);


7.  Find the employee whose salary is closest to the average salary.

SELECT ssn, fname, minit, lname FROM employee e
WHERE ABS(e.salary - (SELECT AVG(salary) FROM employee))
      <= ALL -- we want the difference to be less than all of the others
         (SELECT ABS(e2.salary - (SELECT AVG(salary) FROM employee))
          FROM employee e2);


8.  (CIS 551 only) List the dependents of the 4 lowest paid employees.

SELECT d.dependent_name
FROM employee e1, dependent d
WHERE e1.ssn=d.essn
      AND 4 > (SELECT count(*) FROM employee e2 WHERE e2.salary<e1.salary);