Consider the following SUPPLIER-PARTS-PROJECTS database.
Supplier(sno, sname,status,city)
Part(pno,pname,color,weight,city)
Projects(jno,jname,city)
Spj(sno,pno,jno,qty)
Create Supplier table
CREATE TABLE SUPPLIER1
(
SNO VARCHAR2(5) PRIMARY KEY,
SNAME VARCHAR2(15),
STATUS NUMBER(2),
CITY VARCHAR2(10)
);
Create Part table
CREATE TABLE PART1
(
PNO VARCHAR2(5) PRIMARY KEY,
PNAME VARCHAR2(10),
COLOR VARCHAR2(10),
WEIGHT NUMBER(6,2),
CITY VARCHAR2(10)
);
Create Projects table
CREATE TABLE PROJECTS
(
JNO VARCHAR2(5) PRIMARY KEY,
JNAME VARCHAR2(10),
CITY VARCHAR2(10)
);
Create SPJ table
CREATE TABLE SPJ
(
SNO VARCHAR2(5)REFERENCES SUPPLIER1(SNO),
PNO VARCHAR2(5) REFERENCES PART1(PNO),
JNO VARCHAR2(5) REFERENCES PROJECTS(JNO),
QTY NUMBER(5),
PRIMARY KEY(SNO,PNO,JNO)
);
Description / definition of supplier table
DESC SUPPLIER1;
Name Null? Type
SNO NOT NULL VARCHAR2(5)
SNAME VARCHAR2(15)
STATUS NUMBER(2)
CITY VARCHAR2(10)
Description / definition of part table
DESC PART1;
Name Null? Type
PNO NOT NULL VARCHAR2(5)
PNAME VARCHAR2(10)
COLOR VARCHAR2(10)
WEIGHT NUMBER(6,2)
CITY VARCHAR2(10)
Description / definition of projects table
DESC PROJECTS;
Name Null? Type
JNO NOT NULL VARCHAR2(5)
JNAME VARCHAR2(10)
CITY VARCHAR2(10)
Description / definition of SPJ table
DESC SPJ;
Name Null? Type
SNO NOT NULL VARCHAR2(5)
PNO NOT NULL VARCHAR2(5)
JNO NOT NULL VARCHAR2(5)
QTY NUMBER(5)
Insert statements
INSERT STATEMENT FOR SUPPLIER TABLE
INSERT INTO SUPPLIER1 (SNO,SNAME,STATUS,CITY) VALUES
('S1','KRUNAL',10,'LONDON');
INSERT INTO SUPPLIER1 (SNO,SNAME,STATUS,CITY) VALUES
('S2','RAMESH',5,'INDIA');
INSERT INTO SUPPLIER1 (SNO,SNAME,STATUS,CITY) VALUES
('S3','VIVEK',4,'LONDON');
INSERT INTO SUPPLIER1 (SNO,SNAME,STATUS,CITY) VALUES
('S4','VIMAL',3,'JAPAN');
INSERT INTO SUPPLIER1 (SNO,SNAME,STATUS,CITY) VALUES
('S5','HEMAL',10,'KORIA');
INSERT INTO SUPPLIER1 (SNO,SNAME,STATUS,CITY) VALUES
('S6','RAJU',2,'CHINA');
INSERT INTO SUPPLIER1 (SNO,SNAME,STATUS,CITY) VALUES
('S7','VINU',3,'CHINA');
INSERT STATEMENT FOR PART TABLE
INSERT INTO PART1 (PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P1','PARTA','RED',45,'NEW YORK');
INSERT INTO PART1 (PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P2','PARTB','WHITE',4,'LONDON');
INSERT INTO PART1 (PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P3','PARTC','GREY',24,'CHINA');
INSERT INTO PART1 (PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P4','PARTD','CYCAN',16,'CHINA');
INSERT INTO PART1 (PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P5','PARTE','BLACK',58,'NEW YORK');
INSERT STATEMENT FOR PROJECTS TABLE
INSERT INTO PROJECTS (JNO,JNAME,CITY) VALUES
('J1','PRJ1','LONDON');
INSERT INTO PROJECTS (JNO,JNAME,CITY) VALUES
('J2','PRJ2','CHINA');
INSERT INTO PROJECTS (JNO,JNAME,CITY) VALUES
('J3','PRJ3','CHINA');
INSERT INTO PROJECTS (JNO,JNAME,CITY) VALUES
('J4','PRJ4','INDIA');
INSERT STATEMENT FOR SPJ TABLE
INSERT INTO SPJ (SNO,PNO,JNO,QTY) VALUES
('S1','P2','J1',300);
INSERT INTO SPJ (SNO,PNO,JNO,QTY) VALUES
('S1','P2','J2',800);
INSERT INTO SPJ (SNO,PNO,JNO,QTY) VALUES
('S3','P4','J3',115);
INSERT INTO SPJ (SNO,PNO,JNO,QTY) VALUES
('S4','P2','J4',130);
INSERT INTO SPJ (SNO,PNO,JNO,QTY) VALUES
('S1','P3','J4',75);
Display data of supplier table
SELECT * FROM SUPPLIER1;
Output :
SNO SNAME STATUS CITY
S1 KRUNAL 10 LONDON
S2 RAMESH 5 INDIA
S3 VIVEK 4 LONDON
S4 VIMAL 3 JAPAN
S5 HEMAL 10 KORIA
S6 RAJU 2 CHINA
S7 VINU 3 CHINA
Display data of part table
SELECT * FROM PART1;
Output :
PNO PNAME COLOR WEIGHT CITY
P1 PARTA RED 45 NEW YORK
P2 PARTB WHITE 4 LONDON
P3 PARTC GREY 24 CHINA
P4 PARTD CYCAN 16 CHINA
P5 PARTE BLACK 58 NEW YORK
Display data of project table
Output :
JNO JNAME CITY
J1 PRJ1 LONDON
J2 PRJ2 CHINA
J3 PRJ3 CHINA
J4 PRJ4 INDIA
Display data of SPJ table
Output :
SNO PNO JNO QTY
S1 P2 J1 300
S1 P2 J2 800
S3 P4 J3 115
S4 P2 J4 130
S1 P3 J4 75
Get all shipments where the qty in the range 300 to 750 inclusive.
SELECT * FROM SPJ WHERE QTY BETWEEN 300 AND 750;
Output :
SNO PNO JNO QTY
S1 P2 J1 300
Get all supplier-number, part-number, project number such that they are all co-located.
SELECT A.SNO,B.PNO,C.JNO
FROM SUPPLIER1 A,PART1 B,PROJECTS C,SPJ D
WHERE A.SNO = D.SNO AND B.PNO = D.PNO AND
C.JNO = D.JNO AND A.CITY = B.CITY AND
A.CITY = C.CITY;
Output :
SNO PNO JNO
S1 P2 J1
Get all supplier-number, part-number, project number such that they are all not co-located.
SELECT A.SNO,B.PNO,C.JNO
FROM SUPPLIER1 A,PART1 B,PROJECTS C,SPJ D
WHERE A.SNO = D.SNO AND B.PNO = D.PNO AND
C.JNO = D.JNO AND A.CITY <> B.CITY AND
A.CITY <> C.CITY;
Ouput :
SNO PNO JNO
S3 P4 J3
S4 P2 J4
S1 P3 J4
Get part number of parts supplied by a supplier in London.
SELECT DISTINCT A.PNO AS "Part No."
FROM PART1 A,SUPPLIER1 B,SPJ C
WHERE A.PNO = C.PNO AND
B.SNO = C.SNO AND
B.CITY = 'LONDON';
Output :
Part No.
P2
P3
P4
Get part number of parts supplied by a supplier in London to a project in London.
SELECT DISTINCT A.PNO AS "Part No.",B.SNAME
FROM PART1 A,SUPPLIER1 B,PROJECTS C,SPJ D
WHERE A.PNO = D.PNO AND
B.SNO = D.SNO AND
C.JNO = D.JNO AND
B.CITY = 'LONDON' AND
C.CITY = 'LONDON';
Output :
Part No SNAME
P2 KRUNAL
Get supplier with maximum status.
SELECT * FROM SUPPLIER1
WHERE STATUS =
(SELECT MAX(STATUS) FROM SUPPLIER1);
Output :
SNO SNAME STATUS CITY
S1 KRUNAL 10 LONDON
S5 HEMAL 10 KORIA
Get all pairs of city names such that a supplier in the first city supplies a project in the second city.
SELECT A.CITY AS "Supp.City",B.CITY AS "PROJ.City"
FROM SUPPLIER1 A,PROJECTS B,SPJ C
WHERE A.SNO = C.SNO AND
B.JNO = C.JNO AND
A.CITY <> B.CITY;
Output :
Supp.City PROJ.City
LONDON CHINA
LONDON CHINA
JAPAN INDIA
LONDON INDIA
Get shipment details in the increasing order of supplier numbers.
SELECT * FROM SPJ ORDER BY SNO;
Output :
SNO PNO JNO QTY
S1 P2 J1 300
S1 P2 J2 800
S1 P3 J4 75
S3 P4 J3 115
S4 P2 J4 130
Get all pairs of part numbers such that some supplier supplies both the indicates parts.
SELECT COUNT(SNO),PNO FROM SPJ GROUP BY PNO HAVING COUNT(SNO) > 1;
Output :
COUNT(SNO) PNO
3 P2
Get the total number of project supplier by supplier 1.
SELECT COUNT(PNO) FROM SPJ WHERE SNO = 'S1';
Output :
COUNT(PNO)
3
Get the total quantity of part 1 supplied by supplier 1.
SELECT SUM(QTY) FROM SPJ WHERE SNO = 'S1' AND PNO = 'P2';
Output :
SUM(QTY)
1100
Get part numbers of part supplied to some project in an average quantity of more than 320.
SELECT AVG(QTY),PNO FROM SPJ GROUP BY PNO HAVING AVG(QTY) > 320;
Output :
AVG(QTY) PNO
410 P2
Get supplier numbers from suppliers with a status lower than that of supplier 1.
SELECT SNO FROM SUPPLIER1 WHERE STATUS < (SELECT STATUS FROM SUPPLIER1 WHERE SNO = 'S1');
Output :
SNO
S2
S3
S4
S6
S7