Consider the following schema: Suppliers(sid: integer, sname: string, address: s
ID: 3597050 • Letter: C
Question
Consider the following schema: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, price: real) The Suppliers relation describes suppliers of parts. The Parts relation contains information about each part. The Catalog relation lists the prices in dollars charged for parts by suppliers. (The keys are underlined: sid is a key for Suppliers, (sid,pid) is a key for Catalog, and pid is a key for Parts.)
Write the following queries using SQL.
a) Print all the catalog records for red parts.
b) Find the names of the most expensive parts supplied by suppliers named `Santa Claus’.
c) Find the sids and snames of all the suppliers that supply at least two different parts.
d) For each supplier from (2), print its name and the cost of the least expensive part that supplier carries.
e) Find the sids of suppliers who supply all the blue parts.
Explanation / Answer
a.
select * from parts,catalog where parts.pid=catalog.pid and parts.color='red'
b.
select pname from parts where pid in
(select pid from catalog where price=
(select max(price)from catalog where sid in
(select sid from supplier where sname='Santa Claus')));
c.
SELECT supplier.sname, COUNT(catalog.pid) AS NOS
FROM (catalog
INNER JOIN supplier ON catalog.sid = supplier.sid)
GROUP BY supplier.sid
HAVING COUNT(catalog.pid) > 1
d.
select pname,min(price)from catalog,parts,supplier where catalog.pid=parts.pid and catalog.sid =supplier.sid
e.
select sid from catalog where pid (select pid from parts where color='blue')