Computer Science By Mr. MRK
(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
(G) Assuming that there is one more table COACHES in the database as shown below:
TABLE:COACHES
| SPORTS PERSON | SEX | COACH_NO |
| AJAY | M | 1 |
| SEEMA | F | 2 |
| VINOD | M | 1 |
| TANEJA | F | 3 |
What will be the output of the following query:
SELECT SPORTS PERSON, COACHNAME
FROM CLUB,COACHES
WHERE COACH_ID=COACH_NO
Ans.
| SPORTS PERSON | COACHNAME |
| AJAY | KUKREJA |
| SEEMA | RAVINA |
| VINOD | KUKREJA |
| TANEJA | KARAN |
1999:
5) Given the following Teacher relation: Write SQL commands for questions (b) to (g). TEACHER
| NO | NAME | DEPARTMEN | DATEOF | SALAR | SEX |
| 1 | RAJA | COMPUTER | 21/5/98 | 8000 | M |
| 2 | SANGITA | History | 21/5/97 | 9000 | F |
| 3 | RITU | MATHS | 29/8/98 | 8000 | F |
| 4 | VENKAT | MATHS | 31/10/99 | 8000 | M |
| 5 | SINDU | HISTORY | 21/5/86 | 14000 | F |
| 7 | ASHWARY | MATHS | 11/1/98 | 12000 | F |
