## Questions on SQL

Question 1 : 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 relation lists 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.

Solution : SELECT p.pname FROM Parts AS p WHERE EXISTS ( SELECT * FROM Catalog AS c WHERE c.pid = p.pid)

2. Find the snames of suppliers who supply every red part.

Solution : SELECT s.sid, s.sname FROM Suppliers AS s, Catalog AS c, Parts AS p WHERE s.sid = c.sid AND p.pid = c.pid AND p.color = ‘red’ GROUP BY s.sid, s.sname HAVING COUNT(*) = (SELECT COUNT(*) FROM Parts AS p1 WHERE p1.color=‘red’)

3. Find the snames of suppliers who supply every part.

Solution : 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 ))

4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.

Solution : SELECT p.pname FROM Parts AS p WHERE p.pid IN ( (SELECT c1.pid AS pid FROM Catalog AS c1, Suppliers s1 WHERE c1.pid = s1.sid AND s1.sname = ‘Acme Widget Suppliers’) EXCEPT (SELECT DISTINCT c2.pid AS pid FROM Catalog AS c2, Suppliers s2 WHERE c2.pid = s2.sid AND s2.sname != ‘Acme Widget Suppliers’))

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.

Solution : SELECT DISTINCT c.sid FROM Catalog AS c, (SELECT c1.pid AS avg pid, AVG(c1.cost) AS avg cost FROM Catalog AS c1 GROUP BY c1.pid) AS TEMP WHERE c.pid = TEMP.avg pid AND c.cost > TEMP.avg cost

6. Find the sids of suppliers who supply a red part and a green part.

Solution : (SELECT DISTINCT c1.sid AS SID FROM Parts AS p1, Catalog AS c1 WHERE p1.pid = c1.pid AND p1.color = ‘green’) INTERSECT ( SELECT DISTINCT c2.sid AS SID FROM Parts AS p2, Catalog AS c2 WHERE p2.pid = c2.pid AND p2.color = ‘red’) or SELECT DISTINCT c.sid FROM Catalog c, Parts p WHERE c.pid = p.pid and p.color = 'red' INTERSECT SELECT DISTINCT c.sid FROM Catalog c, Parts p WHERE c.pid = p.pid and p.color = 'green'

7. For every supplier who supplies at least 1 red part, print the name of the supplier and the total number of red parts that he or she supplies.

Solution : SELECT S.sname, COUNT(*) as PartCount FROM Suppliers S, Catalog C, Parts P WHERE C.sid = S.sid and P.pid = C.pid and P.color = 'Red' GROUP BY S.sname, S.sid

8. For each part, find the sname of the supplier who charges the most for that part.

Solution : SELECT P.pid, S.sname FROM Parts P, Suppliers S, Catalog C WHERE C.pid = P.pid AND C.sid = S.sid AND C.cost = (SELECT MAX (C1.cost) FROM Catalog C1 WHERE C1.pid = P.pid)

9. Find the sids of suppliers who supply only red parts.

Solution : SELECT DISTINCT C.sid FROM Catalog C WHERE NOT EXISTS ( SELECT * FROM Parts P WHERE P.pid = C.pid AND P.color <> ‘Red’ )

10. Find the sids of suppliers who supply a red part or a green part.

Solution : 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’

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 supplies.

Solution : SELECT S.sname, MAX(C.cost) as MaxCost FROM Suppliers S, Parts P, Catalog C WHERE P.pid = C.pid AND C.sid = S.sid GROUP BY S.sname, S.sid HAVING ANY ( P.color=’green’ ) AND ANY ( P.color = ’red’ )

12. Find the distinct pnames of all parts.

Solution : SELECT DISTINCT pname FROM Parts;

13. Find the distinct pnames of parts for which there is some supplier.

Solution : SELECT DISTINCT P.pname FROM Parts P, Catalog C WHERE P.pid = C.pid;

14. Find the distinct pnames of all parts sold (by some supplier) for less than 5.50

Solution : SELECT DISTINCT P.pname FROM Parts P, Catalog C WHERE P.pid = C.pid AND C.cost<5.50;

15. Find the price of the least expensive red part.

Solution : SELECT MIN(C.cost) FROM Catalog C, Parts P WHERE C.pid = P.pid AND P.color = 'Red';

16. For every supplier, print the name of the supplier and the total number of parts that he or she supplies.

Solution : SELECT S.sname, COUNT(*) as PartCount FROM Suppliers S, Catalog C WHERE C.sid = S.sid GROUP BY S.sname, S.sid

17. Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.

Solution : SELECT C.pid FROM Catalog C, Suppliers S WHERE S.sname = ‘Yosemite Sham’ AND C.sid = S.sid AND C.cost≥ALL (SELECT C2.cost FROM Catalog C2, Suppliers S2 WHERE S2.sname = ‘Yosemite Sham’ AND C2.sid = S2.sid)

18. For every supplier, print the name and price of the most expensive part that she supplies.

Solution : SELECT S.sname, MAX(C.cost) as MaxCost FROM Suppliers S, Parts P, Catalog C WHERE P.pid = C.pid AND C.sid = S.sid GROUP BY S.sname, S.sid

Previous | Home | Next |

The CREATE VIEW Command | Questions On SQL – Part 2 |

### Incoming search terms:

- edugrabs dbms queries
- find name of suppliers who supply some red part in sql
- parts suppliers catalog sql database testing