## 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:

- state how ma nge was mariage with the sname? story in the bitter gosebetries
- Consider a database that consists of the following relations : SUPPLIER (Sno Sname) PART (Pno Pname) PROJECT (Jno Jname) SUPPLY (Sno Pno Jno) Solve the following queries in SQL : (i) Retrieve the project names (Jname) to whom supplier Sl supplies part P2
- suppliers(sid:integer sname:string address:string) parts(pid:integer pname:string color:string) catalog(sid:integer pid:integer cost:real) i) Find names of suppliers who supply at least one part ii) Find names of suppliers who supply more than three parts
- Suppliers(sid: integer sname: string address: string) Parts(pid: integer pname: string color: string) Catalog(sid: integer pid: integer cost: real)
- find the name of suppliers who supply both in sql query
- \\rdte SQL queries to perform given tasks on follorving schema Suppliers (sid: iL rteger sname: string addrress: string) Parts (pid: integer pnamel strirg color: st ing) Catalog (sid:integer pid: inlegal cost: real) i) Findthe pnames ofparts for rvhich tl
- explain therelational constants in relational data model also consider the following schema;supplier (s_idintegerSname:string address:string) find the name of supplierswho supply some red part
- draw query tree for each query of get names of suppliers who supply
- 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)
- consider a relation statement whose fields are s_id s name s street s city s state s phone course name now write the SQL statement to write