1. Write the SQL Commands and Output of the Following

Practical List

Class XII Chapter- SQL

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

Table: SPORTS

SCode / SportsName / Number / PrizeMoney / ScheduleDate
101
102
103
105
108 / Carom Board
Badminton
Table Tennis
Chess
Lawn Tennis / 2
2
4
2
4 / 5000
12000
8000
9000
25000 / 23-Jan-2012
12-Dec-2011
14-Feb-2012
01-Jan-2012
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
2
3
4
5
6
7
8
9
10 / KUKREJA
RAVINA
KARAN
TARUN
ZUBIN
KETAKI
ANKITA
ZAREEN
KUSH
SHAILYA / 35
34
34
33
36
36
39
37
41
37 / KARATE
KARATE
SQUASH
BASKETBALL
SWIMMING
SWIMMING
SQUASH
KARATE
SWIMMING
BASKETBALL / 27/03/1996
20/01/1998
19/02/1998
01/01/1998
12/01/1998
24/02/1998
20/02/1998
22/02/1998
13/01/1998
19/02/1998 / 1000
1200
2000
1500
750
800
2200
1100
900
1700 / M
F
M
M
M
F
F
F
M
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
MU02
MU15
MD50 / R Jain
H Sinha
S Jha
T Prasad / 2,ABC Appts
12 , Newtown
27/A , Park Street
122-K, SDA / New Dehi
Mumbai
Mumbai
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
FW 05
BS 01
SH 06
FW 12 / Talcom Powder
Face Wash
Bath Soap
Shampoo
Face Wash / LAK
ABC
ABC
XYZ
XYZ / 40
45
55
120
95

Table: CLIENT

C_ID / ClientName / City / P_ID
01
06
12
15
16 / Cosmetic Shop
Total Health
Live Life
Pretty Woman
Dreams / Delhi
Mumbai
Delhi
Delhi
Bengluru / FW05
BS01
Sh06
FW12
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
ND02
MU15
MU50 / R Singhal
Amit Kumar
R Kohli
S Kaur / 24, ABC Enclave
123, Palm Avenue
5 / A , South Street
27 - K, Westend / New Delhi
New Delhi
Mumbai
Mumbai

Table: CONSIGNEE

CneeID / CnorID / CneeName / CneeAddress / CneeCity
MU05
ND08
KO19
MU32
ND48 / ND01
ND02
MU15
ND02
MU50 / Rahul Kishore
P Dhingra
AP Roy
S Mittal
BP Jain / 5, Park Avenue
16 / J , Moore Enclave
2A, Central Avenue
P 245, AB Colony
13, Block DA Vihar / Mumbai
New Delhi
Kolkata
Mumbai
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
2
3
4
5
6
7
8
9
10 / White lotus
Pink feather
Dolphin
Decent
Comfort zone
Donald
Royal finish
Royal Tiger
Econo sitting
Eating Paradise / Double Bed
Baby Cot
Baby Cot
Office Table
Double Bed
Baby Cot
Office Table
Sofa
Sofa
Dining Table / 23/02/02
20/01/02
19/02/02
01/01/02
12/01/02
24/02/02
20/02/02
22/02/02
13/12/01
19/02/02 / 30000
7000
9500
25000
25000
6500
18000
31000
9500
11500 / 25
20
20
30
25
15
30
30
25
25

Table: ARRIVALS

No / ItemName / Type / DateOfStock / Price / Discount
11
12
13 / Wood Comfort
Old Fox
Micky / Double Bed
Sofa
Baby Cot / 23/03/03
20/02/03
21/02/03 / 25000
17000
7500 / 25
20
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
1002
1003
1004
1005
1006 / Ball Pen
Eraser
Pencil
Notebook
Sharpener
Colors / 11
11
12
13
12
11 / 20
10
30
25
15
5 / 10
5
2
20
2
15

Table: SUPPLIERS

Scode / Sname
11
12
13 / Rotomac
Nataraj
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
PL02
ER05
PL01
GP02 / Dot Pen
Pencil
Eraser
Pencil
Gel Pen / ABC
XYZ
XYZ
CAM
ABC / 10
6
7
5
15

Table: CONSUMER

C_ID / ConsumerName / Address / S_ID
01
06
12
15
16 / Good Learner
Write Well
Topper
Writer and Draw
Motivation / Delhi
Mumbai
Delhi
Delhi
Bengalur / PL01
GP02
DP01
PL02
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: