w3resource
SQL exercises

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


Sample Solution:

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;

Sample Output:

     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)

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: Write a query in SQL to count the number of unavailable rooms.
Next: Write a query in SQL to obtain the name of the physicians who are trained for a special treatement.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming