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: physicianemployeeid | 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 | 999999999Sample 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:
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/hospital-database-exercise/sql-exercise-hospital-database-3.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics