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: appointment

Sample table: patient

Sample table: nurse

Sample table: physician

Sample Solution:

-- Selecting the name of the patient, primary physician, and nurse
SELECT pt.name AS "Patient", -- Selecting the name column from the patient table and aliasing it as "Patient"
       p.name AS "Primary Physician", -- Selecting the name column from the physician table and aliasing it as "Primary Physician"
       n.name AS "Nurse" -- Selecting the name column from the nurse table and aliasing it as "Nurse"
-- Joining appointment table with patient table based on patient's SSN
FROM appointment a
JOIN patient pt ON a.patient=pt.ssn
-- Joining appointment table with nurse table based on preparation nurse's employee ID
JOIN nurse n ON a.prepnurse=n.employeeid
-- Joining patient table with physician table based on primary care physician's employee ID
JOIN physician p ON pt.pcp=p.employeeid
-- Filtering rows to include only those where the patient has at least 2 appointments and the nurse is registered
WHERE a.patient IN
    (SELECT patient -- Subquery: Selecting the patient column from the appointment table
     FROM appointment a -- Subquery: Specifies the appointment table and aliases it as "a"
     GROUP BY a.patient -- Subquery: Groups the results by patient
     HAVING count(*)>=2) -- Subquery: Filters the groups to include only those with a count of appointments greater than or equal to 2
  AND n.registered='true' -- Adding a condition to include only registered nurses
ORDER BY pt.name; -- Sorting the results by patient 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.