SQL 3

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

Post a Comment

0 Comments