Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

Supplier-parts-project database

Posted By: Madison Campbell     Category: Oracle     Views: 12320

Article that create supplier-parts-project database with alter, insert, select, update, delete etc...

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

  
Share: 


Didn't find what you were looking for? Find more on Supplier-parts-project database Or get search suggestion and latest updates.

Madison Campbell
Madison Campbell author of Supplier-parts-project database is from Toronto, Canada.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!