INFO20003 Week 7 Lab Solutions Section 1: More SQL
¡ô Task 1.4 Type a query that finds the item ids of the items sold on the second floor.
SELECT DISTINCT saleitem.itemid
FROM saleitem NATURAL JOIN sale NATURAL JOIN department WHERE department.floor = 2
ORDER BY saleitem.itemid;
¡ô Task 1.6 Type a query that for each item, gives its type, the departments that sell the item, and the floor location of these departments.
SELECT DISTINCT item.Name, item.Type, sale.DepartmentID, department.Floor
FROM item
INNER JOIN saleitem ON item.itemID = saleitem.itemID
INNER JOIN sale ON saleitem.saleID = sale.saleID
INNER JOIN department ON sale.departmentID = department.departmentID
ORDER BY item.Name, sale.DepartmentID;
¡ô Task 1.14 The company¡¯s building is going to be renovated. The renovations will include all departments on floors 3 and 4 of the building plus the ¡®Recreation¡¯ department. If additional funding is approved, the rest of floor 2 may be included in the renovation as well. For all employees, print their name and a text value ¡®Yes¡¯, ¡®Maybe¡¯ or ¡®No¡¯ that states whether they will be affected by the renovations. Order by last name.
SELECT FirstName, LastName,
CASE WHEN department.Floor IN (3, 4) OR department.Name = ‘Recreation’
THEN ‘Yes’
WHEN department.Floor = 2 THEN ‘Maybe’ ELSE ‘No’
END AS Affected
FROM employee NATURAL JOIN department ORDER BY LastName;
INFO20003 Week 7 Lab Solutions 1
Section 3: Relational divides using EXISTS and NOT EXISTS
¡ô Task 3.4 Type a query that lists the suppliers that deliver only items sold by the Books department (in other words, suppliers for which every delivery is of an item or items sold by the Books department).
SELECT supplier.Name FROM supplier
WHERE supplierID IN
(SELECT supplierID FROM delivery)
AND NOT EXISTS (SELECT *
FROM deliveryitem NATURAL JOIN delivery
WHERE delivery.supplierID = supplier.supplierID AND itemID NOT IN
(SELECT itemID
FROM saleitem NATURAL JOIN sale NATURAL JOIN department WHERE department.Name = ‘Books’));
This is one of many possible solutions.
INFO20003 Week 7 Lab Solutions 2