SQL First Quize

DATABASE SYSTEMS Quiz Assignment

SQL

Submitted To: Prof. Shoaib Farooq Submitted By: Farhan Saleem Reg. No :

L1F07BSCS2007

University of Central Punjab

Consider the following tables: SUPPLIER ( sid, sname, address ) PARTS ( pid, pname, color ) CATALOG ( sid, pid, cost ) The Catalog table 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. select pname from PARTS,catalog where PARTS.pid=catalog.pid; 2. Find the snames of suppliers who supply every part. select s.sname from supplier s,parts p,calalog c where s.sid=c.sid and p.pid=c.pid; 3. Find the snames of suppliers who supply every red part. select s.sname from supplier s, parts p, catalog c where s.sid=c.sid and c.pid=p.pid and p.color='red'; 4. Find the pnames of parts supplied by Acme Widget Suppliers and by no one else. select p.pname from supplier s, parts p, catalog c where s.sid=c.sid and c.pid=p.pid and s.sname='Acme Widget'; 5. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all suppliers who supply that part). select sid from supplier,catalog where cost in (select max(cost) from catalog group by pid having max(cost) > avg(cost)) 6. For each part, find the sname of the supplier who charges the most for that part.

select sname from supplier,catalog where cost in (select max(cost) from catalog group by pid);

7. Find the sids of suppliers who supply only red parts. select sid from supplier s, parts p, catalog c where s.sid=c.sid and c.pid=p.pid and p.color = 'red';