From the below queries, you need to do the following: 1.The structure and conten
ID: 3756366 • Letter: F
Question
From the below queries, you need to do the following:
1.The structure and contents of the final tables
2.List the SQL statement that answers the query
3.The result of running your query
SupplierNo
Name
Status
City
S1
SMITH
20
LONDON
S2
JONES
10
PARIS
S3
BLAKE
30
PARIS
S4
CLARK
20
LONDON
S5
ADAMS
30
ATHENS
PartNo
Name
Color
Weight
City
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
Name
City
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
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 part names for parts located in London and shipped to any project in LONDON.
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 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 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;
SupplierNo
Name
Status
City
S1
SMITH
20
LONDON
S2
JONES
10
PARIS
S3
BLAKE
30
PARIS
S4
CLARK
20
LONDON
S5
ADAMS
30
ATHENS
Explanation / Answer
Answer)
Create and insert table scrips:
CREATE TABLE Suppliers(SupplierNo text PRIMARY KEY, Name text, Status text, City text);
INSERT INTO Suppliers VALUES('S1','SMITH',20,'LONDON');
INSERT INTO Suppliers VALUES('S2','JONES',10,'PARIS');
INSERT INTO Suppliers VALUES('S3','BLAKE',30,'PARIS');
INSERT INTO Suppliers VALUES('S4','CLARK',20,'LONDON');
INSERT INTO Suppliers VALUES('S5','ADAMS',30,'ATHENS');
CREATE TABLE Parts(PartNo text PRIMARY KEY,Name text,Color text,Weight int,City text);
INSERT INTO Parts VALUES('P1','NUT',
'RED',12,'LONDON');
INSERT INTO Parts VALUES('P2','BOLT',
'GREEN',17,'PARIS');
INSERT INTO Parts VALUES('P3','SCREW',
'BLUE',17,'ROME');
INSERT INTO Parts VALUES('P4','SCREW','RED',
14,'LONDON');
INSERT INTO Parts VALUES('P5','CAMERA','BLUE',32,'PARIS');
INSERT INTO Parts VALUES('P6',
'WRENCH','RED',
19,'LONDON');
INSERT INTO Parts VALUES('P7','C-O-G','
GREEN',12,'ROME');
CREATE TABLE Projects(ProjectNo text primary key,Name text,City text);
INSERT INTO Projects VALUES('J1','SORTER','PARIS');
INSERT INTO Projects VALUES('J2','PUNCH','ROME');
INSERT INTO Projects VALUES('J3','READER','ATHENS');
INSERT INTO Projects VALUES('J4','CONSOLE','ATHENS');
INSERT INTO Projects VALUES('J5','COLLATOR','LONDON');
INSERT INTO Projects VALUES('J6','TERMINAL','OSLO');
INSERT INTO Projects VALUES('J7','TAPE','LONDON');
INSERT INTO Projects VALUES('J8','DRUM','LONDON');
CREATE TABLE SHIPMENTS(SupplierNo,PartNo,ProjectNo,Quantity, FOREIGN KEY (SupplierNo) REFERENCES Suppliers(SupplierNo),FOREIGN KEY (PartNo) REFERENCES Parts(PartNo),FOREIGN KEY (ProjectNo) REFERENCES Projects(ProjectNo), primary key(SupplierNo,PartNo,ProjectNo));
INSERT INTO SHIPMENTS VALUES('S1','P1','J1',200);
INSERT INTO SHIPMENTS VALUES('S1','P1','J4',700);
INSERT INTO SHIPMENTS VALUES('S1','P3','J1',450);
INSERT INTO SHIPMENTS VALUES('S1','P3','J2',210);
INSERT INTO SHIPMENTS VALUES('S1','P3','J3',700);
INSERT INTO SHIPMENTS VALUES('S2','P3','J4',509);
INSERT INTO SHIPMENTS VALUES('S2','P3','J5',600);
INSERT INTO SHIPMENTS VALUES('S2','P3','J6',400);
INSERT INTO SHIPMENTS VALUES('S2','P3','J7',812);
INSERT INTO SHIPMENTS VALUES('S3','P5','J6',750);
INSERT INTO SHIPMENTS VALUES('S3','P3','J2',215);
INSERT INTO SHIPMENTS VALUES('S3','P4','J1',512);
INSERT INTO SHIPMENTS VALUES('S3','P6','J2',313);
INSERT INTO SHIPMENTS VALUES('S4','P6','J3',314);
INSERT INTO SHIPMENTS VALUES('S4','P2','J6',250);
INSERT INTO SHIPMENTS VALUES('S4','P5','J5',179);
INSERT INTO SHIPMENTS VALUES('S4','P5','J2',513);
INSERT INTO SHIPMENTS VALUES('S5','P7','J4',145);
INSERT INTO SHIPMENTS VALUES('S5','P1','J5',269);
INSERT INTO SHIPMENTS VALUES('S5','P3','J7',874);
INSERT INTO SHIPMENTS VALUES('S5','P4','J4',476);
INSERT INTO SHIPMENTS VALUES('S5','P5','J4',529);
INSERT INTO SHIPMENTS VALUES('S5','P6','J4',318);
INSERT INTO SHIPMENTS VALUES('S5','P2','J4',619);
Queries:
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.City='LONDON');
Output:
P1|NUT|RED|12|LONDON
P3|SCREW|BLUE|17|ROME
P5|CAMERA|BLUE|32|PARIS
Get supplier names for suppliers who shipped any part to the COLLATOR project.
SELECT NAME FROM SUPPLIERS
WHERE SupplierNo NOT IN (SELECT SHIPMENTs.SupplierNo
FROM Shipments INNER JOIN PROJECTs
ON Shipments.ProjectNo = PROJECTs.ProjectNo
WHERE PROJECTs.City ='COLLATOR');
Output:
SMITH
JONES
BLAKE
CLARK
ADAMS
Get part names for parts located in London and shipped to any project in LONDON.
SELECT DISTINCT
s.CITY,j.CITY FROM Suppliers s
INNER JOIN Shipments sh ON s.SupplierNo=sh.SupplierNo INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo;
Output:
LONDON|PARIS
LONDON|ATHENS
LONDON|ROME
PARIS|ATHENS
PARIS|LONDON
PARIS|OSLO
PARIS|ROME
PARIS|PARIS
LONDON|OSLO
LONDON|LONDON
ATHENS|LONDON
ATHENS|ATHENS
Get the supplier names for suppliers who did not ship any parts to any project in LONDON.
SELECT s.Name FROM suppliers s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo WHERE j.city<>'LONDON';
Output:
SMITH
SMITH
SMITH
SMITH
SMITH
JONES
JONES
BLAKE
BLAKE
BLAKE
BLAKE
CLARK
CLARK
CLARK
ADAMS
ADAMS
ADAMS
ADAMS
ADAMS
Get the supplier names of suppliers who only shipped parts that weigh less than 15 oz.
SELECT s.Name FROM suppliers s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN parts p on sh.PartNo=p.PartNo
WHERE p.Weight<15;
Output:
SMITH
SMITH
BLAKE
ADAMS
ADAMS
ADAMS
Get all the colors that are not shipped by the supplier S1.
SELECT p.Color FROM suppliers s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN parts p on sh.PartNo=p.PartNo
WHERE s.SupplierNo<>'S1';
Output:
BLUE
BLUE
BLUE
BLUE
BLUE
RED
BLUE
RED
GREEN
BLUE
BLUE
RED
RED
GREEN
BLUE
RED
BLUE
RED
GREEN
Get the supplier names for the suppliers who did not ship any part that is green.
SELECT s.Name FROM suppliers s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN parts p on sh.PartNo=p.PartNo
WHERE p.Color<>'Green';
Output:
SMITH
SMITH
SMITH
SMITH
SMITH
JONES
JONES
JONES
JONES
BLAKE
BLAKE
BLAKE
BLAKE
CLARK
CLARK
CLARK
CLARK
ADAMS
ADAMS
ADAMS
ADAMS
ADAMS
ADAMS
ADAMS
Get project names for projects using at least two parts from supplier S1.
SELECT j.Name 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.Name
HAVING
COUNT(s.SupplierNo)>=2;
Output:
SORTER
Above are the corrected queries and the corresponding outputs.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.