Question

Consider the table DOCTOR given below. Write commands in SQL for (i) to (iv) and output for (v) to (vii).
Table: DOCTOR

Answer 1

(i) To display the names and date of joining of doctors of the oncology department.

• Select DocName, DOJ from Doctor where Department = 'Oncology';

(ii) To display the names and salaries of doctors in descending order of salaries.

• Select DocName, Salary from Doctor order by Salary desc;

(iii) To display the names and salaries of all the female doctors whose salaries are above 50000.

• Select DocName, Salary from Doctor where Gender = 'F' and Salary > 50000;

(iv) To display the names of each department along with the total salary being given to the doctors of that department.

• Select Department, Sum(Salary) from Doctor group by Department;

(v) Output for: Select Department from Doctor where Department = 'Surgery';

$\begin{array}{|c|}\cline{1-1}\tt Department\\\cline{1-1} \sf Surgery\\\cline{1-1}\end{array}$

(vi) Output for: Select Sum(Salary) from Doctor where Department = 'Surgery';

$\begin{array}{|c|}\cline{1-1}\tt Sum(Salary)\\\cline{1-1} \sf 102000\\\cline{1-1}\end{array}$

(vii) Output for: Select DocName, Gender, Department from Doctor where DocName like 'J%';

$\begin{array}{|c|c|c|}\cline{1-3}\tt DocN ame & \tt Gender & \tt Department\\\cline{1-3} \sf Joe\ Thomas & \sf M & \sf Surgery\\\cline{1-3}\end{array}$