Student Name: ____________________
Student ID: ____________________
In-class Exercise 04 SQL
Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The key fields are underlined, and the domain of each field is listed after the field name.
Therefore sid is the key for Suppliers, pid is the key for Parts, and sid and pid together
form the key for Catalog.
Query 1: Find the names of suppliers who supply some red part.
SELECT S.sname
FROM Suppliers S, Parts P, Catalog C
WHERE P.color=’red’ AND C.pid=P.pid AND C.sid=S.sid
πsname(πsid((πpidσcolor=‘red’Parts) Catalog) Suppliers)
Query 2: Find the sids of suppliers who supply some red or green part.
SELECT C.sid
FROM Catalog C, Parts P
WHERE ((P.color = ‘red’ OR P.color = ‘green’) AND P.pid = C.pid)
πsid(πpid(σcolor=‘red’∨color=‘green’Parts) catalog)
Query 3: Find the sids of suppliers who supply some red part or are at ‘221 Packer
Street’
SELECT S.sid
FROM Suppliers S
WHERE S.address = ‘221 Packer street’
OR S.sid IN ( SELECT C.sid
FROM Parts P, Catalog C
WHERE P.color=’red’ AND P.pid = C.pid )
ρ(R1, πsid((πpidσcolor=‘red’Parts) Catalog))
ρ(R2, πsidσaddress=‘221PackerStreet’Suppliers)
R1 ∪ R2
Query 4: Find the sids of suppliers who supply some red part and some green part.
SELECT C.sid
FROM Parts P, Catalog C
WHERE P.pid = C.pid AND P.color='red'
INTERSECT
SELECT C1.sid
FROM Parts P1, Catalog C1
WHERE P1.pid = C1.pid AND P1.color='green'
ρ(R1, πsid((πpidσcolor=‘red’Parts) Catalog))
ρ(R2, πsid((πpidσcolor=‘green’Parts) Catalog))
R1 ∩ R2
Query 5: Find the sids of suppliers who supply every red part.
SELECT S.sid
FROM Suppliers S
WHERE NOT EXISTS ((SELECT P.pid
FROM Parts P
WHERE P.color = 'red‘)
EXCEPT
(SELECT C.pid
FROM Catalog C, Parts P1
WHERE C.sid = S.sid and C.pid=P1.pid and P1.color = 'red'))
Query 6: Find the pids of the most expensive parts supplied by suppliers named ‘Tom’.
Please write down the SQL query.
SELECT C.pid
FROM Catalog C, Suppliers S
WHERE S.sname = ‘Tom’ AND C.sid = S.sid
AND C.cost ≥ ALL (Select C2.cost
FROM Catalog C2, Suppliers S2
WHERE S2.sname = ‘Tom’
AND C2.sid = S2.sid)
Query 7: Find the average cost of all the red parts. Please write down the SQL query.
SELECT AVG(C.cost)
FROM Catalog C, Parts P
WHERE C.pid = P.pid AND P.color = 'red'
Query 8: Find the average cost of each part according to the color (including the same
part supplied by different suppliers). Please write down the SQL query.
SELECT P.color, AVG(C.cost)
FROM Catalog C, Parts P
WHERE C.pid = P.pid
GROUP BY P.color
Query 9: Find the pids of red parts supplied by every supplier. Please write down the
SQL query.
SELECT P.pid
FROM Part P
WHERE NOT EXISTS (SELECT S.sid
FROM Suppliers S
EXCEPT
SELECT C.sid
FROM Catalog C
WHERE C.pid=P.pid)
AND P.color = ‘red’
Query 10: Find the average cost of each part which is supplied by at least two suppliers,
and we only consider the parts with red or green color. Please write down the SQL query.
SELECT C.pid, AVG(C.cost)
FROM Catalog C, Part P
WHERE C.pid = P.pid
AND (P.color = ‘red’ or P.color = ‘green’)
GROUP BY C.pid
HAVING COUNT(*) > 1
