Lab Cycle 2 — SQL Operators, Aggregate Functions, Grouping & Joins

-- 1. Parts with price >= 200 and <= 600 using comparison operators
SELECT PID, Pname, price 
FROM Part 
WHERE price >= 200 AND price <= 600;

-- 2. Suppliers in 'local' or 'global' branch using IN / OR
SELECT SID, Sname, branch 
FROM Supplier 
WHERE branch IN ('local', 'global');

-- 3. Parts with price BETWEEN 200 and 600
SELECT PID, Pname, price 
FROM Part 
WHERE price BETWEEN 200 AND 600;

-- 4. Parts having 'NET' anywhere in pname
SELECT Pname, color 
FROM Part 
WHERE Pname LIKE '%NET%';

-- 5. Parts with pname either 'NUT' or 'BOLT'
SELECT PID, Pname 
FROM Part 
WHERE Pname IN ('NUT', 'BOLT');

-- 6. Suppliers who supplied parts on specified dates
SELECT DISTINCT s.SID, s.Sname, sp.date_supplied
FROM Supplier s
JOIN Supplies sp ON s.SID = sp.SID
WHERE sp.date_supplied IN ('2000-05-01', '2021-01-12', '2000-12-17', '2021-01-10');

-- 7. All distinct costs of parts
SELECT DISTINCT price 
FROM Part 
ORDER BY price ASC;