FOR FREE MATERIALS

CREATE TABLES AND INSERT VALUES

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'));