SQL Exercise: Physicians who is the head of each department

Last update on December 20 2024 12:01:06 (UTC/GMT +8 hours)

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”.

 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

 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:

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:

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.