SQL Exercise: Names of all patients who had at least 2 appointments
SQL hospital Database: Exercise-38 with Solution
38. From the following table, write a SQL query to find those patients with at least two appointments in which the nurse who prepared the appointment was a registered nurse and the physician who provided primary care should be identified. Return Patient name as "Patient", Physician name as "Primary Physician", and Nurse Name as "Nurse".
Sample table: appointmentappointmentid | patient | prepnurse | physician | start_dt_time | end_dt_time | examinationroom ---------------+-----------+-----------+-----------+---------------------+---------------------+----------------- 13216584 | 100000001 | 101 | 1 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | A 26548913 | 100000002 | 101 | 2 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | B 36549879 | 100000001 | 102 | 1 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | A 46846589 | 100000004 | 103 | 4 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | B 59871321 | 100000004 | | 4 | 2008-04-26 10:00:00 | 2008-04-26 11:00:00 | C 69879231 | 100000003 | 103 | 2 | 2008-04-26 11:00:00 | 2008-04-26 12:00:00 | C 76983231 | 100000001 | | 3 | 2008-04-26 12:00:00 | 2008-04-26 13:00:00 | C 86213939 | 100000004 | 102 | 9 | 2008-04-27 10:00:00 | 2008-04-21 11:00:00 | A 93216548 | 100000002 | 101 | 2 | 2008-04-27 10:00:00 | 2008-04-27 11:00:00 | BSample table: patient
ssn | name | address | phone | insuranceid | pcp -----------+-------------------+--------------------+----------+-------------+----- 100000001 | John Smith | 42 Foobar Lane | 555-0256 | 68476213 | 1 100000002 | Grace Ritchie | 37 Snafu Drive | 555-0512 | 36546321 | 2 100000003 | Random J. Patient | 101 Omgbbq Street | 555-1204 | 65465421 | 2 100000004 | Dennis Doe | 1100 Foobaz Avenue | 555-2048 | 68421879 | 3Sample table: nurse
employeeid | name | position | registered | ssn ------------+-----------------+------------+------------+----------- 101 | Carla Espinosa | Head Nurse | t | 111111110 102 | Laverne Roberts | Nurse | t | 222222220 103 | Paul Flowers | Nurse | f | 333333330Sample table: physician
employeeid | name | position | ssn ------------+-------------------+------------------------------+----------- 1 | John Dorian | Staff Internist | 111111111 2 | Elliot Reid | Attending Physician | 222222222 3 | Christopher Turk | Surgical Attending Physician | 333333333 4 | Percival Cox | Senior Attending Physician | 444444444 5 | Bob Kelso | Head Chief of Medicine | 555555555 6 | Todd Quinlan | Surgical Attending Physician | 666666666 7 | John Wen | Surgical Attending Physician | 777777777 8 | Keith Dudemeister | MD Resident | 888888888 9 | Molly Clock | Attending Psychiatrist | 999999999
Sample Solution:
SELECT pt.name AS "Patient",
p.name AS "Primary Physician",
n.name AS "Nurse"
FROM appointment a
JOIN patient pt ON a.patient=pt.ssn
JOIN nurse n ON a.prepnurse=n.employeeid
JOIN physician p ON pt.pcp=p.employeeid
WHERE a.patient IN
(SELECT patient
FROM appointment a
GROUP BY a.patient
HAVING count(*)>=2)
AND n.registered='true'
ORDER BY pt.name;
Sample Output:
Patient | Primary Physician | Nurse ---------------+-------------------+----------------- Dennis Doe | Christopher Turk | Laverne Roberts Grace Ritchie | Elliot Reid | Carla Espinosa Grace Ritchie | Elliot Reid | Carla Espinosa John Smith | John Dorian | Carla Espinosa John Smith | John Dorian | Laverne Roberts (5 rows)
Explanation:
The said query in SQL that selects the names of patients, their primary care physicians, and the names of the nurses who prepared for their appointments. The query only includes patients who have had at least two appointments and whose nurses are registered.
The query uses JOIN statements to link the tables based on the relevant columns. The JOIN statement combines the 'appointment' and 'patient' tables based on the patient and ssn columns. The second JOIN statement combines the 'appointment' and 'nurse' tables based on the presnurse and employeeid columns and the third JOIN statement links the 'patient' and 'physician' tables based on the pcp and employeeid columns.
With the first condition in WHERE clause, only patients with at least two appointments are included in the output, based on a subquery that groups and filters appointments by patient and the second condition ensures that only nurses who are registered are included in the output.
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 who have had a procedure costing over $5,000.
Next SQL Exercise: Providers of primary care who are not department heads.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/hospital-database-exercise/sql-exercise-hospital-database-38.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics