MASTER OF COMPUTER APPLICATIONS
Course Code : MCS-023
Course Title : Introduction to Database Management Systems
Assignment Number : MCA (2)/023/Assign /2014-15
Maximum Marks : 100
Weightage : 25%
We have following relations:
Supplier(S#,sname,status,city) Parts(P#,pname,color,weight,city) SP(S#,P#,quantity)
QUERY for Creating S table:
CREATE TABLE S (S_No CHAR (5) NOT NULL, SNAME CHAR (20) NOT NULL, STATUS SMALLINT NOT NULL, CITY CHAR (15) NOT NULL, PRIMARY KEY (S_No));
QUERY for Creating P table:
CREATE TABLE P (P_No CHAR (5) NOT NULL, PNAME CHAR (20) NOT NULL, COLOR CHAR (20) NOT NULL, WEIGHT INTEGER NOT NULL, CITY CHAR (15) NOT NULL, PRIMARY KEY (P_No));
QUERY for Creating SP table:
CREATE TABLE SP (S_No CHAR (5) NOT NULL, P_No CHAR (5) NOT NULL, QUANTITY)
INTEGER NOT NULL);
Before Starting Query, We need Data inside the Tables Created.
Data for Tables:
P TABLE
P_No PNAME COLOR WEIGHTCITY
P1 Nut Red 12 Delhi
P2 Bolt Green 7 Mumbai
P3 Screw Blue 17 Chennai
P4 Screw Red 14 Delhi
P5 Cam Blue 12 Mumbai
P6 Cog Red 9 Delhi
S TABLE
S_No SNAME STATUS CITY
S1 Smita 20 Delhi
S2 John 10 Mumbai
S3 Babu 30 Mumbai
S4 Candu 20 Delhi
S5 ABHI 30 Pune
SP TABLE
S_No P_No QUANTITY
S1 P1 200
S1 P3 400
S2 P3 200
S2 P5 100
S3 P3 200
S3 P4 500
S4 P6 500
S5 P2 200
S5 P5 100
S5 P6 200
S5 P1 100
S5 P3 200
S5 P4 800
S5 P5 400
S5 P6 500
Answer the following queries in SQL.
1. QUERY for Find name of supplier for city = “Delhi”.
SELECT *
FROM S
WHERE CITY = ‘Delhi’;
2. QUERY for Find suppliers whose name start with “AB”.
SELECT *
FROM S
WHERE NAME LIKE ‘AB%’ ;
3. QUERY for Find all suppliers whose status is 10, 20 or 30.
SELECT *
FROM S
WHERE STATUS BETWEEN 10 AND 30;
4. QUERY for Find total number of city of all suppliers.
SELECT COUNT (*) FROM (SELECT DISTINCT CITY
FROM S);
5. QUERY for Find s# of supplier who supplies “red” part.
SELECT DISTINCT S.S_No
FROM S, SP, P
WHERE S.S_No = SP.S_No
AND SP.P_No = P.P_No AND P.COLOR = ‘Red’;
6. QUERY for Count Number of supplier who supplies “red” part.
SELECT COUNT (*) FROM (SELECT DISTINCT S.S_No
FROM S, SP, P
WHERE S.S_No = SP.S_No
AND SP.P_No = P.P_No AND P.COLOR = ‘Red’);
7. QUERY for Sort the supplier table by sname.
SELECT *
FROM S
ORDER BY SNAME;
8. QUERY for Find name of parts whose color is “red”.
SELECT PNAME
FROM P
WHERE P.COLOR = ‘Red’;
9. QUERY for Find parts name whose weight less than 10 kg.
SELECT PNAME
FROM P
WHERE P.WEIGHT < 10;
10. QUERY for Find all parts whose weight from 10 to 20 kg.
SELECT PNAME, P.WEIGHT
FROM P
WHERE P.WEIGHT BETWEEN 10 AND 20;
11. QUERY for Find average weight of all parts.
SELECT AVG (WEIGHT)
FROM P;
12. QUERY for Find S# of supplier who supply part “p2”.
SELECT S_No
FROM SP
WHERE P_No = ‘P2’;
13. QUERY for Find name of supplier who supply maximum parts.
SELECT SNAME, MAX (QUANTITY)
FROM S, SP, P
WHERE S.S_No = SP.S_No
AND SP.P_No = P.P_No
GROUP BY SNAME;
14. QUERY for Sort the parts table by pname.
SELECT *
FROM P
ORDER BY PNAME;
15. QUERY for Find the parts which weigh 10kg or above and are in “red” colour.
SELECT PNAME
FROM P
WHERE WEIGHT >10
AND COLOR = ‘Red’;