2000 :
5. Write SQL commands for the (b) to (e) and write the outputs for (g) on thse basis of table CLUB.
TABLE: CLUB
| COACH ID |
COACH NAME | AGE | SPORTS | DATE OF APP | PAY | SEX |
|---|---|---|---|---|---|---|
| 1 |
KUKREJA | 35 | KARATE | 27/03/96 | 1000 | M |
| 2 | RAVINA | 34 | KARATE | 20/01/98 | 1200 | F |
| 3 |
KARAN | 34 | SQUASH | 19/01/98 | 2000 | M |
| 4 |
TARUN | 33 | BASKET BAL | 01/01/98 | 1500 | M |
| 5 |
ZUBIN | 36 | SWIMMING | 12/01/98 | 750 | M |
| 6 |
KETAKI | 36 | SWIMMING | 24/02/98 | 800 | F |
| 7 |
ANKITA | 39 | SQUASH | 20/02/98 | 2200 | F |
| 8 | ZAREEN | 37 | KARATE | 22/02/98 | 1100 | F |
| 9 |
KUSH | 41 | SWIMMING | 13/01/98 | 900 | M |
| 10 |
SHAILYA | 37 | BASKETBALL | 19/02/98 | 1700 | M |
(b) To show all information about the swimming coaches in the club.
Ans: Select * from Club
(c) To list names of all coaches with their date of appointment (DATOFAPP) in descending order.
Ans: Select Coachname from Club order by Dataofapp desc
(d) To display a report, showing coachname, pay, age and bonus(15% of pay) for all coaches.
Ans: Select Coachname,Pay,Age,Pay*0.15 from Club
(e) To insert a new row in the CLUB table with following data: 11,”PRAKASH”,37,”SQUASH”, {25/02/98},2500,”M”
Ans: Insert into Club Values (11,”PRAKASH”,37,”SQUASH”,{25/02/98}, 2500,”M”)
(f) Give the output of the following SQL statements:
(i) select COUNT (distinct SPORTS)from CLUB;
Ans: 4
(ii) select MIN(AGE) from CLUB where SEX =”F”;
Ans: 34
(iii) select AVG(PAY) fromCLUB where SPORTS = “KARATE”;
Ans: 1100
(iv) select SUM(PAY) from CLUB where DATAOFAPP>{31/01/98};
Ans: 7800
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( mrkdata@yahoo.com )
