w3resource

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:

E R Diagram: SQL 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook