The document contains SQL statements that create tables for suppliers, parts, projects and shipments and then inserts data into those tables. It also includes several SELECT statements that query the data.
The document contains SQL statements that create tables for suppliers, parts, projects and shipments and then inserts data into those tables. It also includes several SELECT statements that query the data.
The document contains SQL statements that create tables for suppliers, parts, projects and shipments and then inserts data into those tables. It also includes several SELECT statements that query the data.
The document contains SQL statements that create tables for suppliers, parts, projects and shipments and then inserts data into those tables. It also includes several SELECT statements that query the data.
INSERT INTO SUPPLIERS VALUES('S1', 'JOHN',112,'PARIS');
INSERT INTO SUPPLIERS VALUES('S2', 'BLAKE',4,'LONDON');
INSERT INTO SUPPLIERS VALUES('S3', 'JAMES',20,'PARIS');
INSERT INTO SUPPLIERS VALUES('S4', 'SMITH',29,'PARIS');
INSERT INTO SUPPLIERS VALUES('S5', 'CLARK',2,'NEW YORK');
6)
INSERT INTO PARTS VALUES('P1','NUT','GREEN',18,'LONDON');
INSERT INTO PARTS VALUES('P2','BOLT','RED',15,'LONDON');
INSERT INTO PARTS VALUES('P3','SCREW','RED',40,'PARIS');
INSERT INTO PARTS VALUES('P4','SOCKET','YELLOW',8,'NEW YORK');
INSERT INTO PARTS VALUES('P5','SCREW','WHITE',16,'NEW YORK');
7)
INSERT INTO PROJECT VALUES('JI', 'DISPLAY', 'PARIS');
INSERT INTO PROJECT VALUES('J2', 'OCR', 'LONDON');
INSERT INTO PROJECT VALUES('J3', 'CONSOLE', 'LONDON');
INSERT INTO PROJECT VALUES('J4', 'TAPE', 'NEW YORK');
INSERT INTO PROJECT VALUES('J5', 'RAID', 'NEW YORK');
8) INSERT INTO SHIPMENT VALUES('S1','P1','J2',300);
INSERT INTO SHIPMENT VALUES('S2','P2','J1',800);
INSERT INTO SHIPMENT VALUES('S3','P2','J2',415);
INSERT INTO SHIPMENT VALUES('S2','P3','J3',83);
INSERT INTO SHIPMENT VALUES('S4','P4','J5',750);
INSERT INTO SHIPMENT VALUES('S1','P5','J4',225);
9)
10)
SELECT SNO, STATUS FROM SUPPLIERS WHERE SCITY='PARIS' AND STATUS>20;
11)
SELECT SNO, SNAME FROM SUPPLIERS, PARTS, SHIPMENTS WHERE PARTS.PNAME='P2' AND SUPPLIERS.SNO=SHIPMENT.SNO AND PARTS.PNO=SHIPMENT.PNO;
12)
SELECT SNO, SNAME FROM SUPPLIERS, PARTS, SHIPMENT WHERE SUPPLIERS.SNO=SHIPMENT.SNO AND PARTS.PNO=SHIPMENT.PNO AND NOT PARTS.PNAME = 'P2' ORDER BY SNO;
13)
SELECT SNO,SHIPMENT.PNO, JNO PARTS.WEIGHT*SHIPMENT.QUANTITY AS "SHIPMENT' FROM
SHIPMENT, PARTS WHERE PARTS.PNO=SHIPMENT.PNO;
14)
SELECT * FROM SHIPMENT WHERE QUANTITY BETWEEN 300 AND 750;
15)
SELECT DISTINCT PNO FROM PARTS, SUPPLIERS WHERE PARTS.WEIGHT>16 OR SUPPLIERS.SNAME='S2';
16)
SELECT PCITY FROM PARTS, SHIPMENT WHERE PARTS.PNO=SHIPMENT.PNO AND PARTS.COLOUR='RED'
OR SHIPMENT.STATUS>5;
17)
SELECT PNO, PNAME, COLOUR, PCITY, WEIGHT FROM PARTS, SHIPMENT, SUPPLIERS WHERE SUPPLIERS.SNO=SHIPMENT.SNO AND SHIPMENT.PNO=PARTS.PNO AND SUPPLIERS.SCITY='LONDON';
18)
SELECT PNO FROM PARTS, SUPPLIERS, SHIPMENT, PROJECT WHERE PROJECT.JCITY='LONDON' AND SUPPLIERS.SCITY='LONDON' AND SUPPLIERS.SNO=SHIPMENT.SNO AND SHIPMENT.PNO=PARTS.PNO AND PROJECT.JNO=SHIPMENT.JNO;
19)
SELECT COUNT(JNO) FROM SUPPLIERS, SHIPMENT, PROJECT WHERE SUPPLIERS. SNO= SHIPMENT.SNO AND PROJECT.JNO=SHIPMENT.JNO AND SUPPLIERS.SNAME='S1' GROUP BY JNO;
20)
SELECT SUM(QUANTITY) FROM SUPPLIERS, PARTS, SHIPMENT WHERE SUPPLIERS='S1' AND PARTS. NAME='P1' AND SUPPLIERS.SNO=SHIPMENT.SNO AND SHIPMENT.PNO=PARTS.PNO GROUP BY SNO;
