Consider the following schema: Suppliers(sid: integer, sname: string, address: s
ID: 3752438 • 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, cost: real) The Catalog relationlists the prices charged for parts by Suppliers. Write the following queries in SQL: 1. Find the pnames of parts for which there is some supplier. 2. Find the snames of suppliers who supply every part. 3. Find the snames of suppliers who supply every red part. 4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else. 5. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part). 6. For each part, find the sname of the supplier who charges the most for that part. 7. Find the sids of suppliers who supply only red parts. 8. Find the sids of suppliers who supply a red part and a green part. 9. Find the sids of suppliers who supply a red part or a green part. 10. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. 11. for every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she suppliesExplanation / Answer
1. SELECT P.pname FROM Parts P, Catalog C WHERE P.pid=C.pid
2. SELECT S.sname
FROM Suppliers S
WHERE NOT EXISTS ((SELECT P.pid FROM Parts P)
EXCEPT
(SELECT C.pid
FROM Catalog C
WHERE C.sid = S.sid))
3.SELECT S.sname
FROM Suppliers S
WHERE NOT EXISTS ((SELECT * FROM Parts P
WHERE P.color = ‘red’)
EXCEPT
(SELECT C.pid
FROM Catalog C, Parts P
WHERE C.sid = S.sid AND
C.pid = P.pid AND P.color = ‘red’))
4. SELECT P.pname
FROM Parts P, Catalog C, Suppliers S
WHERE P.pid = C.pid AND C.sid = S.sid
AND S.sname = ‘Acme’
AND NOT EXISTS (SELECT * FROM Catalog C1, Suppliers S1
WHERE P.pid = C1.pid AND C1.sid = S1.sid
AND S1.sname <> ‘Acme’
5.SELECT DISTINCT C.sid
FROM Catalog C
WHERE C.cost > (SELECT AVG (C1.cost)
FROM Catalog C1
WHERE C1.pid = C.pid)
6. SELECT P.pid,S.sname
FROM Suppliers S, catalog C
WHERE C.sid = S.sid
AND C.cost = (SELECT MAX (C1.cost)
FROM Catalog C1
WHERE C1.pid = C.pid))
7. SELECT DISTINCT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT * FROM Parts P, Catalog C1
WHERE P.pid = C.pid AND P.color <> ‘red’
AND C.sid = C1.sid)
8.SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid AND P.color = ‘red’
INTERSECT SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color = ‘green’
9. SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid AND P.color = ‘red’
UNION
SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color = ‘green’
10 . SELECT S.name, COUNT(*) as PartCount
FROM Suppliers S, Catalog C, Parts P
WHERE C.sid = S.id AND P.id = C.pid AND P.color = 'GREEN"
GROUP BY S.sname, S.id
11. CREATE VIEW temp2 AS
//Creating a view for suppplier who supplies both red and green
SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.id AND P.color like 'Red%'
INTERSECT //Intersecting Both red and green suppliers
SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid=P1.id AND P1.color like 'Green%';
//Selecting Most expensive and most least supplier from above temp2
SELECT s.sname,Max(c.cost),Min(c.cost)
from temp2,catalog c,suppliers s
where temp2.sid=c.sid and c.sid=s.id
GROUP BY S.sname, S.id
Thank you,
With regards.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.