-- Suppliers Schema

--Suppliers Table

CREATE TABLE SUPPLIERS (

SID NUMBER(5),

SNAME VARCHAR2(20),

ADDRESS VARCHAR2(40)

);

--Parts Table

CREATE TABLE PARTS (

PID NUMBER(5),

PNAME VARCHAR2(20),

COLOR VARCHAR2(40)

);

--Catalog Table

CREATE TABLE CATALOG(

SID NUMBER(5),

PID NUMBER(5),

COST REAL

);

--1. Find the pnames of parts for which there is some supplier.

SELECT DISTINCT P.PNAME

FROM PARTS P, CATALOG C

WHERE P.PID = C.PID

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

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. Find the snames of suppliers who supply every red part.

SELECT S.SNAME

FROM SUPPLIERS S

WHERE NOT EXISTS (( SELECT P.PID

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. Find the pnames of parts supplied by Acme Widget Suppliers and by noone else.

SELECT P.PNAME

FROM PARTS P, CATALOG C, SUPPLIERS S

WHERE P.PID = C.PID AND C.SID = S.SID

AND S.SNAME = ‘ACME WIDGET SUPPLIERS’

AND NOT EXISTS ( SELECT *

FROM CATALOG C1, SUPPLIERS S1

WHERE P.PID = C1.PID AND C1.SID = S1.SID AND

S1.SNAME <> ‘ACME WIDGET SUPPLIERS’ )

-- 5. Find the sids of suppliers who charge more for some part than the average cost of thatpart (averaged

-- over all the suppliers who supply that part).

SELECT DISTINCT C.SID

FROM CATALOG C

WHERE C.COST > ( SELECT AVG (C1.COST)

FROM CATALOG C1

WHERE C1.PID = C.PID )

-- 6. For each part, Find the snameof the supplier who charges the most for that part.

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)

-- 7. Find the sids of suppliers who supply only red parts.

SELECT DISTINCT C.SID

FROM CATALOG C

WHERE NOT EXISTS ( SELECT *

FROM PARTS P

WHERE P.PID = C.PID AND P.COLOR <> ‘RED’ )

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

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. Find the sids of suppliers who supply a red part or a green part

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’

## 0 Comments