w3resource
SQL exercises

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. Write a query in SQL to obtain the name of the physicians with department who are yet to be affiliated.

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)

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

What is the difficulty level of this exercise?



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