Suppliers (SupplierNo, SName, Status, SCity)

Parts (PartNo, PName, Color, Weight, PCity)

Projects (ProjectNo, JName, JCity)

Shipments (SupplierNo, PartNo, ProjectNo, Quantity)

SupplierNo	SName	Status	SCity
S1	SMITH	20	LONDON
S2	JONES	10	PARIS
S3	BLAKE	30	PARIS
S4	CLARK	20	LONDON
S5	ADAMS	30	ATHENS

PartNo	PName	Color	Weight	PCity
P1	NUT	RED	12	LONDON
P2	BOLT	GREEN	17	PARIS
P3	SCREW	BLUE	17	ROME
P4	SCREW	RED	14	LONDON
P5	CAMERA	BLUE	32	PARIS
P6	WRENCH	RED	19	LONDON
P7	C-O-G	GREEN	12	ROME

ProjectNo	JName	JCity
J1	SORTER	PARIS
J2	PUNCH	ROME
J3	READER	ATHENS
J4	CONSOLE	ATHENS
J5	COLLATOR	LONDON
J6	TERMINAL	OSLO
J7	TAPE	LONDON
J8	DRUM	LONDON

SupplierNo	PartNo	ProjectNo	Quantity
S1	P1	J1	200
S1	P1	J4	700
S1	P3	J1	450
S1	P3	J2	210
S1	P3	J3	700
S2	P3	J4	509
S2	P3	J5	600
S2	P3	J6	400
S2	P3	J7	812
S3	P5	J6	750
S3	P3	J2	215
S3	P4	J1	512
S3	P6	J2	313
S4	P6	J3	314
S4	P2	J6	250
S4	P5	J5	179
S4	P5	J2	513
S5	P7	J4	145
S5	P1	J5	269
S5	P3	J7	874
S5	P4	J4	476
S5	P5	J4	529
S5	P6	J4	318
S5	P2	J4	619

Queries

Part-1

Suppliers (SupplierNo, SName, Status, SCity)

Parts (PartNo, PName, Color, Weight, PCity)

Projects (ProjectNo, JName, JCity)

Shipments (SupplierNo, PartNo, ProjectNo, Quantity)

--Get full part-details of all parts that are shipped to any project in LONDON.

SELECT * FROM PARTS WHERE PartNo IN (SELECT Shipments.PartNo

FROM Shipments INNER JOIN PROJECTs ON Shipments.ProjectNo = PROJECTs.ProjectNo

WHERE PROJECTs.JCity='LONDON');

--Get supplier names for suppliers who shipped any part to the COLLATOR project.

SELECT SNAME FROM SUPPLIERS

WHERE SupplierNo NOT IN (SELECT SHIPMENTs.SupplierNo

FROM Shipments INNER JOIN PROJECTs

ON Shipments.ProjectNo = PROJECTs.ProjectNo

WHERE PROJECTs.JCity ='COLLATOR');

--Get all pairs of city names such that a supplier in the first city ships to a project in the second city.

SELECT DISTINCT s.SCITY,j.JCITY FROM Suppliers s INNER JOIN Shipments sh ON s.SupplierNo=sh.SupplierNo

INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo;

--Get supplier name, part name, and project name triples

--such that the indicated supplier, part and project are all co-located (in the same city).

SELECT DISTINCT s.SNAME,j.Jname,P.PName FROM Suppliers s

INNER JOIN Projects j ON s.SCITY=j.JCity

INNER JOIN Parts p ON s.Scity=p.pcity;

--Get part names for parts located in London and shipped to any project in LONDON.

SELECT p.PName FROM parts p INNER JOIN Projects pr ON p.pcity=pr.jcity

WHERE p.pcity='LONDON';

---Part-2

--Get the supplier names for suppliers who did not ship any parts to any project in LONDON.

SELECT s.SName FROM supplier s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo

INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo WHERE j.Jcity<>'LONDON';

--Get the supplier names of suppliers who only shipped parts that weigh less than 15 oz.

SELECT s.SName FROM supplier s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo

INNER JOIN parts p on sh.PartNo=p.PartNo

WHERE p.Weight<15;

--Get all the colors that are not shipped by the supplier S1.

SELECT p.Color FROM supplier s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo

INNER JOIN parts p on sh.PartNo=p.PartNo

WHERE s.SupplierNo<>'S1';

--Get the supplier names for the suppliers who did not ship any part that is green.

SELECT s.SName FROM supplier s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo

INNER JOIN parts p on sh.PartNo=p.PartNo

WHERE p.Color<>'Green';

--Get part numbers for parts that were shipped by a supplier in LONDON

--and that supplier ships only to projects that are located in LONDON.

SELECT P.PartNO FROM Suppliers s

INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo

INNER JOIN Parts p ON sh.PartNo=p.PartNo

INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo

WHERE s.Scity = 'LONDON' and j.jcity = 'LONDON' and

s.SCITY not in (SELECT distinct Scity FROM suppliers WHERE scity <>'LONDON');

--Get project names for projects that were sent shipments by both suppliers S1 and S2.

SELECT j.JName FROM Suppliers s

INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo

INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo

WHERE s.SupplierNo in ('S1','S2');

--Get project names for projects that were not sent any shipments either supplier S1 or S2.

SELECT j.JName FROM Suppliers s

INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo

INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo

WHERE s.SupplierNo NOT in ('S1','S2');

--Part-3

Get project names for projects using at least two parts from supplier S1.

SELECT j.JName FROM Suppliers s

INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo

INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo

WHERE s.SupplierNo = 'S1'

GROUP BY j.JName HAVING COUNT(s.SupplierNo)>=2;

--Get part names for parts shipped by at least 2 different suppliers in LONDON.

SELECT P.PName FROM Suppliers s

INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo

INNER JOIN Parts p ON sh.PartNo=p.PartNo

WHERE s.Scity = 'LONDON'

GROUP BY P.PName HAVING COUNT(s.SupplierNo) >=2;

--Get the names of suppliers who shipped the part with maximum weight (compared to all other parts).

SELECT s.SName FROM Suppliers s

INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo

INNER JOIN Parts p ON sh.PartNo=p.PartNo

GROUP BY s.SName HAVING p.Weight= (SELECT MAX(Weight) FROM Parts);

--Get the suppliers details for suppliers who supplied parts to only one project.

SELECT s.SName FROM Suppliers s

INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo

INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo

WHERE s.SupplierNo = 'S1'

GROUP BY s.SName HAVING COUNT(j.ProjectNo)=1;

QUESTION: If the FINAL result in above contains no records, what records do you need to add to the tables or modify to show at least one single record in the final table? What is the FINAL result after you add these records?