Question

SUPPLIER

Sno Sname Status City

s1 Smith 20 London

s2 Jones 10 Paris

s3 Blake 30 Paris

s4 Clark 20 London

s5 Adams 30 NULL

PART

Pno Pname Color Weight City

p1 Nut Red 12 London

p2 Bolt Green 17 Paris

p3 Screw NULL 17 Rome

p4 Screw Red 14 London

p5 Cam Blue 12 Paris

p6 Cog Red 19 London

SHIPMENT

Sno Pno Qty Price

s1 p1 300 .005

s1 p2 200 .009

s1 p3 400 .004

s1 p4 200 .009

s1 p5 100 .01

s1 p6 100 .01

s2 p1 300 .006

s2 p2 400 .004

s3 p2 200 .009

s3 p3 200 NULL

s4 p2 200 .008

s4 p3 NULL NULL

s4 p4 300 .006

s4 p5 400 .003

QUESTIONS (SOLUTIONS MUST BE SQL QUERY IN SQL SERVER):

1. Print supplier numbers for suppliers who ship at least all those parts shipped by supplier S3. Do not include S3 in the answer and do not "count".

2. Print supplier numbers for suppliers who ship ONLY red parts.

Answer 1

TABLE CREATION AND VALUES INSERTION:

CREATE TABLE SUPPLIER(Sno varchar(5) PRIMARY KEY, Sname varchar(20),Status integer,City varchar(20));

INSERT INTO SUPPLIER VALUES('s1','Smith',20,'London');
INSERT INTO SUPPLIER VALUES('s2','Jones',10,'Paris');
INSERT INTO SUPPLIER VALUES('s3','Blake',30,'Paris');
INSERT INTO SUPPLIER VALUES('s4','Clark',20,'London');
INSERT INTO SUPPLIER VALUES('s5','Adams',30,NULL);

CREATE TABLE PART(Pno varchar(5) PRIMARY KEY,Pname varchar(20),Color varchar(10),Weight integer,City varchar(20));

INSERT INTO PART VALUES('p1','NUT','Red',12,'London');
INSERT INTO PART VALUES('p2','Bolt','Green',17,'Paris');
INSERT INTO PART VALUES('p3','Screw',NULL,17,'Rome');
INSERT INTO PART VALUES('p4','Screw','Red',14,'London');
INSERT INTO PART VALUES('p5','Cam','Blue',12,'Paris');
INSERT INTO PART VALUES('p6','Cog','Red',19,'London');

CREATE TABLE SHIPMENT(Sno varchar(5), Pno varchar(5), Qty integer, Price varchar(10), FOREIGN KEY(Sno) REFERENCES SUPPLIER(Sno), FOREIGN KEY(Pno) REFERENCES PART(Pno));

INSERT INTO SHIPMENT VALUES('s1','p1',300,'.005');
INSERT INTO SHIPMENT VALUES('s1','p2',200,'.009');
INSERT INTO SHIPMENT VALUES('s1','p3',400,'.004');
INSERT INTO SHIPMENT VALUES('s1','p4',200,'.009');
INSERT INTO SHIPMENT VALUES('s1','p5',100,'.01');
INSERT INTO SHIPMENT VALUES('s1','p6',100,'.01');
INSERT INTO SHIPMENT VALUES('s2','p1',300,'.006');
INSERT INTO SHIPMENT VALUES('s2','p2',400,'.004');
INSERT INTO SHIPMENT VALUES('s3','p2',200,'.009');
INSERT INTO SHIPMENT VALUES('s3','p3',200,NULL);
INSERT INTO SHIPMENT VALUES('s4','p2',200,'.008');
INSERT INTO SHIPMENT VALUES('s4','p3',NULL,NULL);
INSERT INTO SHIPMENT VALUES('s4','p4',300,'.006');
INSERT INTO SHIPMENT VALUES('s4','p5',400,'.003');

2).SELECT r.Sno FROM SUPPLIER r where r.Sno IN (SELECT Sno from SHIPMENT s where s.Pno IN (Select p.Pno from PART p where p.color='Red'));

OUTPUT:

Sno
s1
s2
s4