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 ( mrkdata@yahoo.com )
