Chapter – 8 . STRUCTURED QUERY LANGUAGE

(vii) SELECT DESIGNATION, SUM(SALARY) FROM DESIG GROUP BY DESIGNATION HAVING
COUNT (*) < 3;

Ans. Designation Sum(Salary) Director 85000 Salesman 60000

(viii) SELECT SUM(BENIFTS) FROM DESIG WHERE DESIGINATION =”salesman”;

Ans. 15000

2004 Question Paper

5. Give the following table for database a LIBRARY

TABLE : BOOKS

5. b) Write the SQL commands for (i) to (vii) on the basis of the table SPORTS

TABLE: SPORTS

(i) Display the names of the students who have grade ‘C’ in either Game1 or Game2 or both.

Ans. Select Name From Sports Where Grade1=”C” OR Grade2=”C”

(ii) Display the number of students getting grade ‘A’ in Cricket.

Ans. Select Count(*) from Sports Where (Game1=”Cricket” and Grade1=”A”) or (Game2=”Cricket” and Grade2=”A”)

(iii) Display the names of the students who have same game for both game1 and game2

Ans. Select Name From Sports Where Game1=Game2

(iv) Display the games taken up by the students, whose name starts with ‘A’.

Ans. Select Game1,Game2 From Sports Where Name Like “A%”

(v) Add a new column named ‘marks’.

Ans. Alter Table Sports Add Marks Number

(5)
(vi) Assign a value 200 for marks for all those who are getting grade ‘B’ or ‘A’ in both Game1 and Game2.

Ans. (Children, Try This Answer as an assignment)

(vii) Arrange the whole table in the alphabetical order of name.

Ans. Select * from Sports Order By Name 2000 :

5. Write SQL commands for the (b) to (e) and write the outputs for (g) on thse basis of table CLUB.

TABLE: CLUB

Computer Science By Mr. MRK