COMPUTER SCIENCE STRUCTURED QUERY LANGUAGE

DELHI 2006:

5.b) Study the following tables DOCTOR and SALARY and write SQL commands for the questions (i) to (iv) and give outputs for SQL queries (v) to (vi) :

TABLE: DOCTOR

ID NAME DEPT SEX EXPERIENCE
101 Johan ENT   M   12
104 Smith ORTHOPEDIC   M     5
107 George CARDIOLOGY   M   10
114 Lara SKIN   F     3
109 K George MEDICINE   F     9
105 Johnson ORTHOPEDIC   M   10
117 Lucy ENT   F     3
111 Bill MEDICINE   F   12
130 Murphy ORTHOPEDIC   M   15

TABLE: SALARY

ID
BASIC ALLOWANCE CONSULTAION
101 12000   1000   300
104 23000   2300   500
107 32000   4000   500
114 12000   5200   100
109 42000   1700   200
105 18900   1690   300
130 21700   2600   300

(i) Display NAME of all doctors who are in “MEDICINE” having more than 10 years experience from the Table DOCTOR.

Ans: Select Name from Doctor where Dept=”Medicine” and Experience>10

(ii) Display the average salary of all doctors working in “ENT”department using the tables. DOCTORS and SALARY Salary =BASIC+ALLOWANCE.

Ans: Select avg(basic+allowance) from Doctor,Salary where Dept=”Ent” and Doctor.Id=Salary.Id

(iii) Display the minimum ALLOWANCE of female doctors.

Ans: Select min(Allowance) from Doctro,Salary where Sex=”F” and Doctor.Id=Salary.Id

(iv) Display the highest consultation fee among all male doctors.

Ans: Select max(Consulation) from Doctor,Salary where Sex=”M” and Doctor.Id=Salary.Id

(v) SELECT count (*) from DOCTOR where SEX = “F”

Ans: 4

(vi) SELECT NAME, DEPT , BASIC from DOCTOR, SALRY Where DEPT = “ENT” AND DOCTOR.ID = SALARY.ID

Ans:      Name           Dept Basic
            Jonah Ent        12000

 

CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( [email protected] )