Answer the following queries in SQL – IGNOU MCA Assignment 2014 – 15

By | August 2, 2014

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’;

SQL_Query_01

2.  QUERY for Find suppliers whose name start with “AB”.

SELECT *
FROM S
WHERE NAME LIKE ‘AB%’ ;

SQL_Query_02

3.  QUERY for Find all suppliers whose status is 10, 20 or 30.

SELECT *
FROM S
WHERE STATUS BETWEEN 10 AND 30;

SQL_Query_03

4.  QUERY for Find total number of city of all suppliers.

SELECT COUNT (*) FROM (SELECT DISTINCT CITY
FROM S);

SQL_Query_04

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’;

 SQL_Query_05
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’);

SQL_Query_06

7.  QUERY for Sort the supplier table by sname.

SELECT *
FROM S
ORDER BY SNAME;

SQL_Query_07

8.  QUERY for Find name of parts whose color is “red”.

SELECT PNAME
FROM P
WHERE P.COLOR = ‘Red’;

SQL_Query_08
 
9.  QUERY for Find parts name whose weight less than 10 kg.

SELECT PNAME
FROM P
WHERE P.WEIGHT < 10;

SQL_Query_09
 
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;

SQL_Query_10
 
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’;

SQL_Query_12
 
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;

SQL_Query_13
 
14.  QUERY for Sort the parts table by pname.

SELECT *
FROM P
ORDER BY PNAME;

SQL_Query_14
 
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’;

 SQL_Query_15

Leave a Reply