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


Sample table: department


Sample Solution:


-- Selecting the 'name' column from the 'department' table, renaming it as "Department",
-- and the 'name' column from the 'physician' table, renaming it as "Physician",
-- where the 'head' column in the 'department' table matches the 'employeeid' column in the 'physician' table
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

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.