Question

INRO TO DATABASES

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 using join, nested queries and set operators.

1. Find names of suppliers who supply every red or green part.

2. Find the sids of suppliers who supply every red part or supply every green part.

3. Find sids of suppliers who supply only red parts.

4. Find the pids of parts that are supplied by at least two different suppliers.

5. Find the sid of suppliers who charge more for some parts than the average price of that part.

Answer 1

Ans 1).

Select Suppliers.sid from Suppliers where not exists

(

(select Parts.pid from Parts where Parts.color=''red' or P.color=''green'')

except

(select Catalog.pid from Catalog where Catalog.sid=Suppliers.sid)

)

Ans 2).

(Select Suppliers.sid from Suppliers

where not exists

(

(select Parts.pid from Parts where Parts.color=''red')

except

(select Catalog.pid from Catalog where Catalog.sid=Suppliers.sid)

)

)

UNION

(select Suppliers.sid from Suppliers

where not exists

(

(select Parts.pid from Parts where Parts.color='green')

except

(select Catalog.pid from Catalog where Catalog.sid=Suppliers.sid)

)

)

Ans 3).

Select Suppliers.sid from Suppliers where not exists

(

(Select Parts.pid from Parts where Parts.color=''red')

except

(select Catalog.pid from Catalog where Catalog.sid=Suppliers.sid)

)

Ans 4).

select Catalog.pid from Catalog where exists

(

Select Catalog1.sid from Catalog1 where Catalog1.pid = Catalog.pid and Catalog1.sid != Catalog.sid

)

Ans 5).

Select sid, avg(cost) from Suppliers where cost>Avg(cost) group by pid;