Question

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): i) Display NAME of all doctors who are in “MEDICINE” having more than 10 years experience from the table DOCTOR. ii) Display the average salary of all doctors working in “ENT” department using the tables DOCTOR and SALARY. Salary = BASIC + ALLOWANCE iii) Display the minimum ALLOWANCE of female doctors. iv) Display the highest consultation fee among all male doctors. v) To display records of all the doctors in ascending order of experience. vi) SELECT count( * ) from DOCTOR where SEX = “F” vii) SELECT NAME, DEPT, BASIC from DOCTOR, SALARY where DEPT = “ENT” and DOCTOR.ID = SALARY.ID

Answer 1

$\huge\tt\red{AnsWer~ -}$

(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

Answer 2

Answer:

1) Select Name from Doctor where Dept=”Medicine” and Experience>10

The above comment gives the names of all doctors in medicine department with experience of more than 10 years.

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

The above comment gives the average salary of all doctors working in ENT department.

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

The above comment gives the minimum allowance of all the female doctors.

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

The above comment displays the maximum consultation fee among all male doctors

5) Select from doctor ORDER BY Experience ASC;

The above comment gives the doctor's name by experience in ascending order.

6) 4

Count of female doctors.

7) Name Dept Basic

Jonah Ent 12000