SQL exercises on hospital Database: Find the name of the physician and the departments they are affiliated with
SQL hospital Database: Exercise-8 with Solution
8. Write a query in SQL to obtain the name of the physician and the departments they are affiliated with.
Sample table: physician
Sample table: department
Sample table: affiliated_with
SELECT p.name AS "Physician", d.name AS "Department" FROM physician p, department d, affiliated_with a WHERE p.employeeid=a.physician AND a.department=d.departmentid;
Physician | Department -------------------+------------------ John Dorian | General Medicine Elliot Reid | General Medicine Christopher Turk | General Medicine Christopher Turk | Surgery Percival Cox | General Medicine Bob Kelso | General Medicine Todd Quinlan | Surgery John Wen | General Medicine John Wen | Surgery Keith Dudemeister | General Medicine Molly Clock | Psychiatry (11 rows)
E R Diagram of Hospital Database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?