Class – XI SQL ASSIGNMENT (TO BE DONE IN PRACTICAL FILE)
Q1. Write SQL commands for the following queries based on the relation
Teacher given below:
No Name Age Dept DOJ Salary Gender
1 Jugal 34 Computer 10/01/97 12000 M
2 Sharmila 31 History 24/03/98 20000 F
3 Sandeep 32 Maths 12/12/96 30000 M
4 Sangeeta 35 History 01/07/99 40000 F
5 Rakesh 42 Maths 05/09/97 25000 M
6 Shyam 50 History 27/06/98 30000 M
7 Shiv Om 44 Computer 25/02/97 21000 M
8 Shalakha 33 Maths 31/07/97 20000 F
a) To show all information about the teacher of History department.
b) To list the names of female teachers who are in Maths department.
c) To list the names of all teachers with their date of joining in ascending order.
d) To display teacher’s name, salary, age for male teachers only.
e) To display the details of the teachers with Age more than 23 in descending
order of their salary.
f) to display the names of departments .
g) to display the details of female teachers whose name starts with s.
h) Increase the age of all the teachers by 1 year.
i) Increase the age of all the male teachers by 1 year whose age is above 50.
j) Increase the salary of all females by 15 %
k) Delete the details of all teacher with age above 55.
Q2. Write SQL commands for the following queries on the basis of Club relation given below:
Coach-ID CoachName Age Sports date_of_app 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
a) To show all information about the swimming coaches in the club.
b) To list the names of all coaches with their date of appointment in
descending order.
c) To display a report showing coach name, pay, age, and bonus (15% of pay)
for allcoaches.
d) To insert a new row in the Club table with ANY relevant data.
e) To display Coach name, age and sports of the coaches of Karate, Squash and
Basketball only.
f) To display the details of all male coaches earning between 1500 and 2500.
g) Delete the records of all coaches having age above 40.
h) To display the details of the coaches whose name starts with K and ends with a.
i) To display coach name, pay, age and gender in order of their coach id.
j) Increase the pay of all female coaches by 15%.
[Link] SQL commands :
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
a. To show all information about the Baby cots from the FURNITURE table.
b. To list the ITEMNAME which are priced at more than 15000 from the FURNITURE table.
c. To list ITEMNAME and TYPE of those items, in which date of stock is before
22/01/02from the FURNITURE table in descending of ITEMNAME.
d. To display ITEMNAME and DATEOFSTOCK of those items, in which the
discount percentage is more than 25 from FURNITURE table.
e. To display item details whose TYPE is "Sofa" from FURNITURE table.
f. To insert a new row in the ARRIVALS table with the following data:
14,“Valvet touch”, "Double bed", {25/03/03}, 25000,30
g. Add a new column Fcode in the table and make it primary key.
h. Delete the furniture records which all were stocked in the year 2001.
i. To show the item name,type and discounted price .(discounted price is price *
discount /100.) .
j. Display the details of the firnitures having price in the range 10000 to 25000 in
descending order of their price.