w3resource

SQL Exercise: Physicians who is the head of each department

SQL hospital Database: Exercise-3 with Solution

3. From the following tables, write a SQL query to identify the physicians who are the department heads. Return Department name as “Department” and Physician name as “Physician”.

Sample table: physician
 employeeid |       name        |           position           |    ssn
------------+-------------------+------------------------------+-----------
          1 | John Dorian       | Staff Internist              | 111111111
          2 | Elliot Reid       | Attending Physician          | 222222222
          3 | Christopher Turk  | Surgical Attending Physician | 333333333
          4 | Percival Cox      | Senior Attending Physician   | 444444444
          5 | Bob Kelso         | Head Chief of Medicine       | 555555555
          6 | Todd Quinlan      | Surgical Attending Physician | 666666666
          7 | John Wen          | Surgical Attending Physician | 777777777
          8 | Keith Dudemeister | MD Resident                  | 888888888
          9 | Molly Clock       | Attending Psychiatrist       | 999999999
Sample table: department
 departmentid |       name       | head
--------------+------------------+------
            1 | General Medicine |    4
            2 | Surgery          |    7
            3 | Psychiatry       |    9

Sample Solution:

SELECT d.name AS "Department",
       p.name AS "Physician"
FROM department d,
     physician p
WHERE d.head=p.employeeid;

Sample Output:

    Department    |  Physician
------------------+--------------
 General Medicine | Percival Cox
 Surgery          | John Wen
 Psychiatry       | Molly Clock
(3 rows)

Explanation:

The said query in SQL that selects the "name" column alias as "Department" from the 'department' table and the "name" column alias as "physician" from the 'physician' table, but only for those physicians who are the head of their respective department.

This query uses an implicit join to combine the 'department' and 'physician' tables.

The "WHERE" clause specifies the condition that the "employeeid" of a physician should match the "head" column of their respective department .

Pictorial presentation:

Find the name of the physicians who are the head of each department

Alternative Solution:

Using INNER JOIN Syntax:


SELECT d.name AS "Department",
       p.name AS "Physician"
FROM department d
JOIN physician p ON d.head = p.employeeid;

Explanation:

This query uses the INNER JOIN syntax to join the department and physician tables based on the condition d.head = p.employeeid. It selects the department name (d.name) and physician name (p.name) in the result.

Practice Online


E R Diagram of Hospital Database:

E R Diagram: SQL Hospital Database.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the nurse who are the head of their department.
Next SQL Exercise: Patients with at least one physician appointment.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.