CBSE Class 12 Informatics Practices
Question 16 of 79
MySQL SQL Revision Tour — Question 9
Back to all questions 9
Question Write SQL commands for the following on the basis of given table CLUB.
Table : CLUB
| COACH_ID | COACHNAME | AGE | SPORTS | DATOFAPP | 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 names of all coaches with their date of appointment (DATOFAPP) in descending order.
(c) To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.
(a)
SELECT *
FROM CLUB
WHERE SPORTS = 'SWIMMING' ;+----------+-----------+-----+----------+-----+-----+------------+
| COACH_ID | COACHNAME | AGE | SPORTS | PAY | SEX | DATOFAPP |
+----------+-----------+-----+----------+-----+-----+------------+
| 5 | ZUBIN | 36 | SWIMMING | 750 | M | 1998-01-12 |
| 6 | KETAKI | 36 | SWIMMING | 800 | F | 1998-02-24 |
| 9 | KUSH | 41 | SWIMMING | 900 | M | 1998-01-13 |
+----------+-----------+-----+----------+-----+-----+------------+
(b)
SELECT COACHNAME, DATOFAPP
FROM CLUB
ORDER BY DATOFAPP DESC ;+-----------+------------+
| COACHNAME | DATOFAPP |
+-----------+------------+
| KETAKI | 1998-02-24 |
| ZAREEN | 1998-02-22 |
| ANKITA | 1998-02-20 |
| KARAN | 1998-02-19 |
| SHAILYA | 1998-02-19 |
| RAVINA | 1998-01-20 |
| KUSH | 1998-01-13 |
| ZUBIN | 1998-01-12 |
| TARUN | 1998-01-01 |
| KUKREJA | 1996-03-27 |
+-----------+------------+
(c)
SELECT COACHNAME, PAY, AGE, (PAY * 0.15) AS BONUS
FROM CLUB ;+-----------+------+-----+--------+
| COACHNAME | PAY | AGE | BONUS |
+-----------+------+-----+--------+
| KUKREJA | 1000 | 35 | 150.00 |
| RAVINA | 1200 | 34 | 180.00 |
| KARAN | 2000 | 34 | 300.00 |
| TARUN | 1500 | 33 | 225.00 |
| ZUBIN | 750 | 36 | 112.50 |
| KETAKI | 800 | 36 | 120.00 |
| ANKITA | 2200 | 39 | 330.00 |
| ZAREEN | 1100 | 37 | 165.00 |
| KUSH | 900 | 41 | 135.00 |
| SHAILYA | 1700 | 37 | 255.00 |
+-----------+------+-----+--------+