Chapter – 8 . STRUCTURED QUERY LANGUAGE

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