SQL exercises on hospital Database: Find the name of the physicians with department who are yet to be affiliated

SQL hospital Database: Exercise-10 with Solution

10. From the following tables, write a SQL query to find those physicians who are yet to be affiliated. Return Physician name as "Physician", Position, and department as "Department".

Sample table: physician

Sample table: affiliated_with

Sample table: department

Sample Solution:

SELECT p.name AS "Physician",
       p.position,
       d.name AS "Department"
FROM physician p
JOIN affiliated_with a ON a.physician=p.employeeid
JOIN department d ON a.department=d.departmentid
WHERE primaryaffiliation='false';

Sample Output:

    Physician     |           position           |    Department
------------------+------------------------------+------------------
 Christopher Turk | Surgical Attending Physician | General Medicine
 John Wen         | Surgical Attending Physician | General Medicine
(2 rows)

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: From the following tables, write a SQL query to find those physicians who have trained for special treatment. Return Physician name as "Physician", treatment procedure name as "Treatment".
Next: From the following tables, write a SQL query to find those physicians who are not a specialized physician. Return Physician name as "Physician", position as "Designation".