Chapter – 8 . STRUCTURED QUERY LANGUAGE

(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

What will be the output of the following query:
SELECT SPORTS PERSON, COACHNAME
FROM CLUB,COACHES
WHERE COACH_ID=COACH_NO

Ans.

1999:

5) Given the following Teacher relation: Write SQL commands for questions (b) to (g). TEACHER

Computer Science By Mr. MRK