Practical List

Class XII Chapter- SQL

1. Write the SQL commands and output of the following:

Table: SPORTS
SCode SportsName Number PrizeMoney ScheduleDate
101 Carom Board 2 5000 23-Jan-2012
102 Badminton 2 12000 12-Dec-2011
103 Table Tennis 4 8000 14-Feb-2012
105 Chess 2 9000 01-Jan-2012
108 Lawn Tennis 4 25000 19-Mar-2012

i) To display the name of all sports with their SCode.

ii) To display details of those sports which are having PrizeMoney more than
9000.
iii) To display the contents of the SPORTS table in ascending order of
ScheduleDate.
iv) To display sum of PrizeMoney for each of the Number of participation
groupings
v) SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM SPORTS.

2. Write the SQL commands and output of the following:

Table: CLUB
CoachID Coach Name Age Sports DateOfApp Pay Sex
1 KUKREJA 35 KARATE 27/03/1996 1000 M
2 RAVINA 34 KARATE 20/01/1998 1200 F
3 KARAN 34 SQUASH 19/02/1998 2000 M
4 TARUN 33 BASKETBALL 01/01/1998 1500 M
5 ZUBIN 36 SWIMMING 12/01/1998 750 M
6 KETAKI 36 SWIMMING 24/02/1998 800 F
7 ANKITA 39 SQUASH 20/02/1998 2200 F
8 ZAREEN 37 KARATE 22/02/1998 1100 F
9 KUSH 41 SWIMMING 13/01/1998 900 M
10 SHAILYA 37 BASKETBALL 19/02/1998 1700 M
 i) To show all information about the swimming coaches in the table.
ii) To list names of all coaches with their fate of appointment (DateOfApp) in
descending order.
iii) To display a report, showing Coach Name, Pay,Age and bonus(15% of pay)
for all the coaches.
iv) To insert a new row in the table CLUB with the following data:
11,”PRAKASH”,37,”SQUASH”,{25/02/1998},2500,M
v) SELECT COUNT (DISTINCT SPORTS) FROM CLUB;
3. Write the SQL commands and output of the following:

Table: SENDER
SenderID SenderName SenderAddress SenderCity
ND01 R Jain 2,ABC Appts New Dehi
MU02 H Sinha 12 , Newtown Mumbai
MU15 S Jha 27/A , Park Street Mumbai
MD50 T Prasad 122-K, SDA New Delhi

Table: RECIPIENT
RecID SenderID RecName RecAddress RecCity
KD05 ND01 R Bajpayee 5, Central Avenue Kolkata
ND08 MU02 S Mahajan 116, A Vihar New Delhi
MU19 ND01 H Singh 2A, Andheri East Mumbai
MU32 MU15 P K Swamy B5, CS Terminus Mumbai
ND45 ND50 S Tripathi 13,BID, Mayur Vihar New Delhi

i) To display the name of all senders from Mumbai.

ii) To display the RecID, SenderName , SenderAddress , RescName ,
RecAddress for every recipient.
iii) To display recipient details in ascending order of RecName.
iv) To display Number of Recipients from each city.
v) SELECT DISTINCT SenderCity FROM SENDER;

4. Write the SQL commands and output of the following:

Table: PRODUCT
 P_ID ProductName ManufacturerName Price
TP 01 Talcom Powder LAK 40
FW 05 Face Wash ABC 45
BS 01 Bath Soap ABC 55
SH 06 Shampoo XYZ 120
FW 12 Face Wash XYZ 95

Table: CLIENT
C_ID ClientName City P_ID
01 Cosmetic Shop Delhi FW05
06 Total Health Mumbai BS01
12 Live Life Delhi Sh06
15 Pretty Woman Delhi FW12
16 Dreams Bengluru TP01

i) To display the details of those clients whose City is Delhi.

ii) To display the details of product whose Price is in the range of 50 to 100
(both values included).
iii) To display the ClientName, City from table CLIENT and ProductName and
Price from table PRODUCT, with their corresponding matching P_ID .
iv) To increase the price of all product by 10.
v) SELECT DISTINCT City FROM CLIENT;

5. Write the SQL commands and output of the following:

Table: CONSIGNOR
CnorID CnorName CnorAddress City
ND01 R Singhal 24, ABC Enclave New Delhi
ND02 Amit Kumar 123, Palm Avenue New Delhi
MU15 R Kohli 5 / A , South Street Mumbai
MU50 S Kaur 27 - K, Westend Mumbai

Table: CONSIGNEE
CneeID CnorID CneeName CneeAddress CneeCity
MU05 ND01 Rahul Kishore 5, Park Avenue Mumbai
ND08 ND02 P Dhingra 16 / J , Moore Enclave New Delhi
 KO19 MU15 AP Roy 2A, Central Avenue Kolkata
MU32 ND02 S Mittal P 245, AB Colony Mumbai
ND48 MU50 BP Jain 13, Block DA Vihar New Delhi

i) To display all consignors from Mumbai.

ii) To display the CneeID , CnorName , CnorAddress , CneeName ,
CneeAddress for every consignee.
iii) To display consignee details in ascending order of CneeName .
iv) To display number of consignors from each city.
v) SELECT CneeName,CneeAddress FROM CONSIGNEE WHERE CneeCity
NOT IN (“Mumbai”,”Kolkata”);

6. Write the SQL commands and output of the following:

Table: FURNITURE
No ItemName Type DateOfStock Price Discount
1 White lotus Double Bed 23/02/02 30000 25
2 Pink feather Baby Cot 20/01/02 7000 20
3 Dolphin Baby Cot 19/02/02 9500 20
4 Decent Office Table 01/01/02 25000 30
5 Comfort zone Double Bed 12/01/02 25000 25
6 Donald Baby Cot 24/02/02 6500 15
7 Royal finish Office Table 20/02/02 18000 30
8 Royal Tiger Sofa 22/02/02 31000 30
9 Econo sitting Sofa 13/12/01 9500 25
10 Eating Paradise Dining Table 19/02/02 11500 25
Table: ARRIVALS
No ItemName Type DateOfStock Price Discount
11 Wood Comfort Double Bed 23/03/03 25000 25
12 Old Fox Sofa 20/02/03 17000 20
13 Micky Baby Cot 21/02/03 7500 15
i) To list the ItemName which are priced at more than 15000 from the
FURNITURE table.
ii) To display ItemName,Type,DateOfStock of those items, in which DISCOUNT
percentage is more than 28 from the FURNITURE table.
iii) To count the number of items whose type is “Baby Cot” from FURNITURE
table .
iv) To insert a new row in the ARRIVALS table with the following data:
14,”Velvet touch”,”Sofa”,{26/03/05},15000,20
v) SELECT MAX(Discount) FROM FURNITURE,ARRIVALS;

7. Write the SQL commands and output of the following:

Table: STATIONARY
 ItemNo Item Scode Qty Rate
1001 Ball Pen 11 20 10
1002 Eraser 11 10 5
1003 Pencil 12 30 2
1004 Notebook 13 25 20
1005 Sharpener 12 15 2
1006 Colors 11 5 15

Table: SUPPLIERS
Scode Sname
11 Rotomac
12 Nataraj
13 Rainbow

i) To display detail of all the items in the table STATIONARY in ascending order
of Qty.
ii) To display ItemNo,Item name of those items from table STATIONARY whose
Rate is more than 15.
iii) To display detail of those items whose Scode is 12 or Qty in Stationary is
more than 10 from the table STATIONARY.
iv) To display minimum Rate of items for each supplier individually as per Scode
from the table STATIONARY:
v) SELECT Rate*Qty FROM STATIONARY WHERE ItemNo = 1001;

8. Write the SQL commands and output of the following:

Table: STATIONARY
S_ID StationaryName Company Price
DP01 Dot Pen ABC 10
PL02 Pencil XYZ 6
ER05 Eraser XYZ 7
PL01 Pencil CAM 5
GP02 Gel Pen ABC 15

Table: CONSUMER
C_ID ConsumerName Address S_ID
 01 Good Learner Delhi PL01
06 Write Well Mumbai GP02
12 Topper Delhi DP01
15 Writer and Draw Delhi PL02
16 Motivation Bengalur PL01

i) To display the details of those consumers whose Address is Delhi.

ii) To display the details of STATIONARY whose Price is in range of 8 to
15(both values included).
iii) To display the ConsumerName, Address from table CONSUMER and
company and Price from table STATIONARY, with their corresponding
matching S_ID.
iv) To increase the price of all stationary by 2.
v) SELECT DISTINCT Address FROM CONSUMER;

9. Write the SQL commands and output of the following:

Table: PRODUCT
P_ID ProductName ManufacturerName Price
TP 01 Talcom Powder LAK 40
FW 05 Face Wash ABC 45
BS 01 Bath Soap ABC 55
SH 06 Shampoo XYZ 120
FW 12 Face Wash XYZ 95

Table: CLIENT
C_ID ClientName City P_ID
01 Cosmetic Shop Delhi FW05
06 Total Health Mumbai BS01
12 Live Life Delhi Sh06
15 Pretty Woman Delhi FW12
16 Dreams Bengluru TP01

i) To display the details of those clients whose City is Delhi.

ii) To display the details of product whose Price is in the range of 50 to 100
(both values included).
 iii) To display the ClientName, City from table CLIENT and ProductName and
Price from table PRODUCT, with their corresponding matching P_ID .
iv) To increase the price of all product by 10.
v) SELECT DISTINCT City FROM CLIENT;

10. Write the SQL commands and output of the following:

Table: SPORTS
SCode SportsName Number PrizeMoney ScheduleDate
101 Carom Board 2 5000 23-Jan-2012
102 Badminton 2 12000 12-Dec-2011
103 Table Tennis 4 8000 14-Feb-2012
105 Chess 2 9000 01-Jan-2012
108 Lawn Tennis 4 25000 19-Mar-2012

i) To display the name of all sports with their SCode.

ii) To display details of those sports which are having PrizeMoney more than
9000.
iii) To display the contents of the SPORTS table in ascending order of
ScheduleDate.
iv) To display sum of PrizeMoney for each of the Number of participation
groupings
v) SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM SPORTS.