SQL Exercise: Find physicians 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)

Explanation:

The said query in SQL that selects the name, position, and department of physicians who are not primary-affiliated with a department from the table physician, affiliated_with, and department.

The query links the physician and affiliated_with table based on the physician and employeeid columns, and affiliated_with and department table based on the department and departmentid columns. The affiliated_with table contains the foreign keys to the physician and department tables.

The WHERE clause filters the results and return only those physicians who are not primary-affiliated with a department.

The resulting output will be a table with the column headers labeled "Physician", "Position", and "Department", respectively.

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: Physicians who are trained in a special treatment.
Next SQL Exercise: Name of the physicians who are not a specialized.