4 Assignment Problem Set
1.(a) UPDATE Ingredients SET quantity = 16 WHERE ingredient = butter AND quantity > 16; (b) DELETE FROM Ingredients WHERE ingredient = pork AND food IN (SELECT food FROM Dish WHERE cuisine = Arab); (c) INSERT INTO ShoppingList SELECT d.cuisine, i.ingredient, SUM(i.quantity) FROM Dish d, Ingredient i WHERE d.food = i.food GROUP BY d.cuisine, i.ingredient; (d) 2.(a) SELECT * FROM Employee E1 WHERE salary >= ALL (SELECT salary FROM Employee E2 WHERE E2.manager=E1.manager) OR salary <= ALL (SELECT salary FROM Employee E2 WHERE E2.manager=E1.manager); (b) SELECT location, AVG(salary) FROM Employee GROUP BY location; (c) SELECT manager FROM Employee E1 GROUP BY manager HAVING COUNT(DISTINCT location)>2; (d) SELECT manager, SUM(salary) FROM Employee GROUP BY manager;
3.(a) SELECT owner FROM Cars WHERE owner NOT IN (SELECT owner FROM Motorcycle); (b) SELECT owner, COUNT(*) FROM Cars GROUP BY owner; (c) INSERT INTO Ownership (SELECT c.owner, COUNT(DISTINCT c.license), COUNT(DISTINCT m.license) FROM Cars c, Motorcycle m WHERE c.owner=m.owner GROUP BY c.owner) UNION (SELECT c.owner, COUNT(*), 0 FROM Cars c WHERE c.owner NOT (SELECT IN owner FROM Motorcycle) GROUP BY c.owner) UNION (SELECT m.owner, 0, COUNT(*) FROM Motorcycle m WHERE m.owner NOT IN (SELECT owner FROM Cars) GROUP BY m.owner); 4.(a) SELECT course FROM Instructors WHERE instructor = Prof. Smith; (b) SELECT instructor, COUNT(*) FROM Instructors GROUP BY instructor; (c) SELECT instuctor, COUNT(DISTINCT course) FROM Instructors GROUP BY instructor; 5.(a) SELECT t.student FROM Transcript t, Instuctors i WHERE t.course = i.course AND t.quarter = i.quarter AND i.instructor = Prof. Smith;
(b) SELECT student FROM Transcript t WHERE NOT EXISTS (SELECT course FROM Instructors WHERE instructor = Prof. Smith AND course NOT IN (SELECT course FROM Transcript WHER...