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:
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics