For the following database schema: Write queries that: a) List part numbers for
ID: 3708577 • Letter: F
Question
For the following database schema:
Write queries that:
a) List part numbers for parts supplied to all projects in Paris.
b) List project numbers for projects supplied with at least all the parts available from supplier S1
c) List project numbers for projects supplied entirely by supplier S1
d) List supplier that only supplies the parts to the projects located in Paris
S (S#, SNAME, STATUS, CITY) - Primary key: S#
P (P#, PNAME, COLOR, WEIGHT, CITY) - Primary key P#
J (J#, JNAME, CITY) - Primary key J#
SPJ (S#, P#, J#, QTY) - Primary key S#,P#,J#
ADDITIONAL INFO:
S – Supplier
P- Part
J - PROJECT
SPJ – SUPPLIER-PART-PROJECT (The ternary relationship)
Explanation / Answer
/*a) List part numbers for parts supplied to all projects in Paris.*/
SELECT P# FROM P WHERE P# = ALL (SELECT P# FROM SPJ WHERE S# IN (SELECT S# FROM S WHERE City='Paris'))
/*b) List project numbers for projects supplied with at
least all the parts available from supplier S1*/
SELECT J# FROM J WHERE J# =ANY (SELECT J# FROM SPJ WHERE S#='S1')
/*c) List project numbers for projects supplied entirely by supplier S1*/
SELECT J#
FROM J
WHERE J# NOT IN (
SELECT J#
FROM SPJ
WHERE S# <> 'S1');
/*d) List supplier that only supplies the parts to the projects located in Paris*/
SELECT S.* FROM S
INNER JOIN SPJ ON S.S#=SPJ.S#
INNER JOIN P ON SPJ.P#=P.P#
WHERE S.S# NOT IN ( SELECT S# FROM S WHERE city<>'Paris');