Lab Cycle 3 — Set Operations, Advanced/Recursive CTEs & TCL

-- 1. UNION: Combine supplier cities and another query
SELECT city FROM Supplier
UNION
SELECT branch FROM Supplier;

-- 2. INTERSECT: Cities that are also branch names
SELECT city FROM Supplier
INTERSECT
SELECT branch FROM Supplier;

-- 3. MINUS / EXCEPT: Supplier IDs that have NOT made any supplies
SELECT SID FROM Supplier
EXCEPT
SELECT SID FROM Supplies;

-- 4. Cartesian Product (Cross Join)
SELECT s.SID, s.Sname, p.PID, p.Pname
FROM Supplier s
CROSS JOIN Part p;

-- 5. List all parts except 'NUT' and 'BOLT' in ascending order of costs
SELECT PID, Pname, price
FROM Part
WHERE Pname NOT IN ('NUT', 'BOLT')
ORDER BY price ASC;

-- 6. Display all parts that have not been supplied so far
SELECT PID, Pname FROM Part
EXCEPT
SELECT p.PID, p.Pname FROM Part p JOIN Supplies sp ON p.PID = sp.PID;

-- 7. Suppliers who supplied 'green' part with cost 500 AND 'red' part with cost 400
SELECT s.Sname
FROM Supplier s
JOIN Supplies sp ON s.SID = sp.SID
JOIN Part p ON sp.PID = p.PID
WHERE p.color = 'green' AND p.price = 500
INTERSECT
SELECT s.Sname
FROM Supplier s
JOIN Supplies sp ON s.SID = sp.SID
JOIN Part p ON sp.PID = p.PID
WHERE p.color = 'red' AND p.price = 400;

-- 8. Suppliers who have supplied ALL parts that are 'red' in color (Relational Division)
SELECT s.Sname
FROM Supplier s
JOIN Supplies sp ON s.SID = sp.SID
JOIN Part p ON sp.PID = p.PID
WHERE p.color = 'red'
GROUP BY s.SID, s.Sname
HAVING COUNT(DISTINCT p.PID) = (
    SELECT COUNT(*) FROM Part WHERE color = 'red'
);