SQL Exercise: Patients and their physicians who do not need a nurse

SQL hospital Database: Exercise-18 with Solution

18. From the following tables, write a SQL query to identify those patients and their physicians who do not require any nursing assistance. Return Name of the patient as "Name of the patient", Name of the Physician as "Name of the physician" and examination room as "Room No.".

Sample table: patient

Sample table: appointment

Sample table: physician

Sample Solution:

SELECT t.name AS "Name of the patient",
       p.name AS "Name of the physician",
       a.examinationroom AS "Room No."
FROM patient t
JOIN appointment a ON a.patient=t.ssn
JOIN physician p ON a.physician=p.employeeid
WHERE a.prepnurse IS NULL;

Sample Output:

 Name of the patient | Name of the physician | Room No.
---------------------+-----------------------+----------
 John Smith          | Christopher Turk      | C
 Dennis Doe          | Percival Cox          | C
(2 rows)

Explanation:

The said query in SQL that returns information about appointments where no preparatory nurse was assigned, including the patient's name, the physician's name, and the examination room number.

The JOIN keyword performs a join between the 'patient', 'appointment', and 'physician' tables based on their relational columns.

The 'appointment' and the 'patient' tables are joins based on the patient and ssn columns, and the 'appointment' and 'physician' tables are joins based on the the physician and employeeid columns.

The WHERE clause filters the results by appointments where no preparatory nurse was assigned. This is determined by checking whether the preparatory nurse identifier in the 'appointment' table is NULL.

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: Patients with an appointment on the given date.
Next SQL Exercise: Names of patients, their doctors, and medications.