SQL Exercise: Patients and doctors who gave them preliminary care.

SQL hospital Database: Exercise-13 with Solution

13. From the following tables, write a SQL query to identify the patients and the number of physicians with whom they have scheduled appointments. Return Patient name as "Patient", number of Physicians as "Appointment for No. of Physicians".

Sample table: appointment

Sample table: patient

Sample Solution:

SELECT p.name "Patient",
       count(t.patient) "Appointment for No. of Physicians"
FROM appointment t
JOIN patient p ON t.patient=p.ssn
GROUP BY p.name
HAVING count(t.patient)>=1;

Sample Output:

      Patient      | Appointment for No. of Physicians
-------------------+-----------------------------------
 Grace Ritchie     |                                 2
 John Smith        |                                 3
 Dennis Doe        |                                 3
 Random J. Patient |                                 1
(4 rows)

Explanation:

The said query in SQL that retrieves the number of appointments made by each patient, along with the count of how many physicians each patient has seen.

The table appointment aliases as t and the table patient aliases as p are used to simplify the syntax.

The JOIN statement links the appointment table with the patient table using a matching condition where the patient's SSN matches the appointment's patient ID.

The GROUP BY statement ensures that the count function aggregates the appointment count for each patient, while the HAVING statement applies a condition to include only those patients who have had at least one appointment.

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: Find the patients and the physicians who treated them.
Next SQL Exercise: Count unique patients who came to examination room C.