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

Stu dno	ClassName	Game1	Grade1	Game2	Grade2
10 7	Smeer	Criket	B	Swimming	A
11 8	Sujit	Tennis	A	Skating	C
12 7	Kamala	Swimming	B	Football	B
13 7	Veena	Tennis	C	Tennis	A
14 9	Archana	Basket ball	A	Cricket	A

(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

COAC-ID	COACH NAME	AGE	SPORTS DATEOF	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