1. CREATE TABLE SUPPLIER
(
SNO VARCHAR2(20) CONSTRAINT PK_SUPPLIER PRIMARY KEY,
SNAME VARCHAR2(20) NOT NULL,
STATUS NUMBER(4) NOT NULL,
CITY VARCHAR2(20)
);
1. DESC SUPPLIER;
1. INSERT INTO TABLE SUPPLIER :
INSERT INTO SUPPLIER VALUES('S01','AJIT',1701,'KOLKATA');
INSERT INTO SUPPLIER VALUES('S02','HARJIT',1023,'GURGAON');
INSERT INTO SUPPLIER VALUES('S03','BITTU',1563,'PATNA');
INSERT INTO SUPPLIER VALUES('S04','VENKAT',1369,'BANGALORE');
INSERT INTO SUPPLIER VALUES('S05','SURESH',1000,'DELHI');
1. SELECT *FROM SUPPLIER;
2. CREATE TABLE PARTS
(
PNO VARCHAR2(20) CONSTRAINT PK_PARTS PRIMARY KEY,
PNAME VARCHAR2(20) NOT NULL,
COLOR VARCHAR2(20),
WEIGHT INTEGER,
CITY VARCHAR2(20)
);
2. DESC PARTS;
2. INSERT INTO TABLE PARTS :
INSERT INTO PARTS VALUES ('P01','CABINET','BLACK',70,'GURGAON');
INSERT INTO PARTS VALUES ('P02','WALLPAPER','RED',2,'KOLKATA');
INSERT INTO PARTS VALUES ('P03','KNOBS','GOLD',1,'KOLKATA');
INSERT INTO PARTS VALUES ('P04','LINING','RED',1,'BANGALORE');
INSERT INTO PARTS VALUES ('P05','SHELVES','BROWN',3,'DELHI');
2. SELECT *FROM PARTS;
3. CREATE TABLE SHIPMENT
(
SNO VARCHAR2(20) CONSTRAINT FK_SNO_SHIPMENT REFERENCES SUPPLIER(SNO),
PNO VARCHAR2(20) CONSTRAINT FK_PNO_SHIPMENTS REFERENCES PARTS(PNO),
QTY INTEGER
);
3. DESC SHIPMENT;
3. TABLE SHIPMENT :
INSERT INTO SHIPMENT VALUES ('S01','P01',10);
INSERT INTO SHIPMENT VALUES ('S01','P05',85);
INSERT INTO SHIPMENT VALUES ('S02','P03',150);
INSERT INTO SHIPMENT VALUES ('S03','P01',15);
INSERT INTO SHIPMENT VALUES ('S03','P04',250);
INSERT INTO SHIPMENT VALUES ('S04','P02',45);
INSERT INTO SHIPMENT VALUES ('S04','P03',190);
INSERT INTO SHIPMENT VALUES ('S04','P05',26);
INSERT INTO SHIPMENT VALUES ('S05','P04',31);
3. SELECT *FROM SHIPMENT;
QUERIES :
1. GET SUPPLIER NAMES FOR SUPPLIERS WHO SUPPLY ALL RED PARTS :
SELECT DISTINCT(SUPPLIER.SNAME) FROM SUPPLIER, PARTS, SHIPMENT WHERE
SUPPLIER.SNO=SHIPMENT.SNO AND PARTS.PNO=SHIPMENT.PNO AND PARTS.COLOR = 'RED';
2. GET SUPPLIER NAMES FOR SUPPLIERS WHO SUPPLY ALL PARTS :
SELECT SNAME FROM SUPPLIER WHERE SNO IN
(SELECT SNO FROM SHIPMENT GROUP BY SNO HAVING COUNT(PNO)
IN (SELECT MAX(COUNT(PNO)) FROM SHIPMENT GROUP BY SNO));
3.GET SUPPLIER NAMES FOR SUPPLIERS WHO SUPPLY AT LEAST ONE RED PARTS :
SELECT DISTINCT(SNAME) FROM SUPPLIER WHERE SNO IN
(SELECT SNO FROM SHIPMENT WHERE PNO IN
(SELECT PNO FROM PARTS WHERE COLOR = 'RED'));