DBMS Lab Assignment 2 (JOIN)

Practical Lab Assignment based on Database Management System

BCA – II SEMESTER

Supplier-Parts-Projects Database

I.            Create the following  tables S, P, J and SPJ for the Supplier-Parts-Projects Database

with the following structures:

 S                       SUPPLIER  

Field name	Data type	Constraints
SNO	Varchar(5)	Primary key
SNAME	varchar(25)	NOT NULL
STATUS	int	NOT NULL
CITY	varchar(20)	NOT NULL

                                                                         

P                         PART

Field name	Data type	Constraints
PNO	Varchar(6)	Primary key
PNAME	varchar(25)	NOT NULL
COLOR	varchar(10)	NOT NULL
WEIGHT	Numeric(5,1)	NOT NULL
CITY	varchar(20)	NOT NULL

J                   PROJECT

Field name	Data type	Constraints
JNO	Varchar(6)	Primary key
JNAME	varchar(25)	NOT NULL
CITY	varchar(20)	NOT NULL

SPJ              SHIPMENT

Field name	Data type	Constraints
SNO	Varchar(5)	NOT NULL, Foreign key SNO references S
PNO	Varchar(6)	NOT NULL, Foreign key  PNO references P
JNO	varchar(6)	NOT NULL, Foreign key   JNO references J
QTY	int	NOT NULL

II.            Insert the following values to the tables S, P, J & SPJ.

S

Sno	SNAME	STATUS	CITY
S1	SMITH	20	LONDON
S2	JONES	10	PARRIS
S3	BLAKE	30	PARRIS
S4	CLARK	20	LONDON
S5	ADAMS	30	ATHENS

P

Pno	PNAME	COLOR	WEIGHT	CITY
P1	NUT	RED	12	LONDON
P2	BOLT	GREEN	17	PARIS
P3	SCREW	BLUE	17	ROME
P4	SCREW	RED	14	LONDON
P5	CAM	BLUE	12	PARIS
P6	COG	RED	19	LONDON

J

Jno	JNAME	CITY
J1	SORTER	PARIS
J2	DISPLAY	ROME
J3	OCR	ATHENS
J4	CONSOLE	ATHENS
J5	RAID	LONDON
J6	EDS	OSLO
J7	TAPE	LONDON

SPJ

Sno	Pno	Jno	QTY
S1	P1	J1	200
S1	P1	J4	700
S2	P3	J1	400
S2	P3	J2	200
S2	P3	J3	200
S2	P3	J4	500
S2	P3	J5	600
S2	P3	J6	400
S2	P3	J7	800
S2	P5	J2	100
S3	P3	J1	200
S3	P4	J2	500
S4	P6	J3	300
S4	P6	J7	300
S5	P2	J2	200
S5	P2	J4	100
S5	P5	J5	500
S5	P5	J7	100
S5	P6	J2	200
S5	P1	J4	100
S5	P3	J4	200
S5	P4	J4	800
S5	P5	J4	400
S5	P6	J4	500

III.               Write the following queries in SQL.

       

Based on Simple Queries:

1.                           Get the full details of the suppliers.

2.                           Get the full details of the parts.

3.                           Get the full details of the projects.

4.                           Get the full details of the shipments.

5.                           Get the full details of the projects in London.

6.                           Get all shipments where the quantity is in the range 300 to 750 inclusive.

7.                           Get supplier no and status for suppliers in PARIS , in descending order of status.

8.                           Get part nos. for red parts and weight is greater than equals to 14.

9.                           Get the supplier numbers for suppliers who supply project J1.

10.                       Get supplier numbers for suppliers who supply project J1, in supplier number

order.

Based on Aggregate Functions:

11.              Get the total no. of suppliers.

12.              Get the maximum and minimum quantity for each part.

13.              Get the maximum and minimum quantity for part p2.

14.              Get part numbers of parts supplied to some project in an average quantity of more than 350.

15.              Get the total number of projects supplied by S1.

16.              Get the total quantity of part p1 supplied to supplier s1.

17.              For each part supplied, get the part no and the total shipment quantity.

18.              For each part being supplied to a project, get the part no, project no.  and the corresponding total shipment quantity.

19.              Get the part no. and the total no. of  supplier supplied to the shipment.

20.              Get the part no. and the total no. of  supplier supplied to the shipment having  more than 2 supplier.

Based on Joins Queries:

21.  Get suppliers names who supply part p3.

22.  Get distinct suppliers names who supply part p3.

23.  Get the supplier number, supplier names for suppliers who supply project J1.

24.  Get part numbers of parts supplied by a supplier in London.

25.  Get part numbers of parts supplied by a supplier in London to a project in London.

Based on Special Data

26.  Get all shipments where the quantity is non-null.

27.  Get project numbers and cities where the city has an "o" as the second letter of its name.

Based on Subqueries

28.              Get project names for project supplied by supplier S1.

29.              Get colors for parts supplied by supplier S1.

30.              Get part numbers for parts supplied to any project in London.

31.              Get project numbers for project using at least one part available from supplier S1.

32.              Get supplier numbers for suppliers supplying at least one part supplied by at least one supplier who supplies at least one red part.

33.              Get supplier numbers for suppliers with status lower than that of supplier S1.

34.              Get project numbers for project whose city is first in the alphabetic list of such cities.

35.              Get the second maximum weight from parts.

36.              Get the second minimum status from suppliers.

Based on Update Operations:  

37.  Change the color of all red parts to orange.

38.  Insert a new supplier (S10) into table S. The name and city are Smith and New York,  

      respectively; the status is yet known.

39. Get all the red parts rows in P and insert the result into a table named RedParts. 

      Assume the table exists.

Based on EXISTS 

40.  Get part numbers fro parts supplied to any project in London.

Download Here: https://drive.google.com/file/d/0By5qxXmfg1UrdjZDMnVFZ0FUbUE/edit?usp=sharing