SQL Final Review #1: Patient and Diagnosis Tables

By /

This SQL practice note uses a simple healthcare example with two tables:
Patients and Diagnoses. The goal is to review basic SQL concepts such as
SELECT, WHERE, JOIN, GROUP BY, LEFT JOIN, NOT EXISTS, and subqueries.

Sample Tables

Patients

PatientID PatientName Age City
101 Alice Kim 72 Seattle
102 John Lee 65 Bellevue
103 Sarah Park 58 Seattle
104 David Choi 75 Tacoma

Diagnoses

DiagnosisID PatientID DiagnosisCode
1 101 E11
2 101 I10
3 102 E11
4 104 N18

Diagnosis Codes:
E11 = Diabetes
I10 = Hypertension
N18 = Chronic Kidney Disease

1. Basic SELECT

Question: Display PatientName, Age, and City for all patients.

SELECT PatientName, Age, City
FROM Patients;

Healthcare Connection:
This is like retrieving a simple patient list from a healthcare database.

2. WHERE Clause

Question: Show all patients who live in Seattle.

SELECT PatientName, Age, City
FROM Patients
WHERE City = 'Seattle';

Mental Picture:
Start with all patients, then filter only the patients who live in Seattle.

3. Aggregate Function: COUNT

Question: Count how many total patients are in the Patients table.

SELECT COUNT(*) AS TotalPatients
FROM Patients;

Healthcare Connection:
This is similar to counting the total number of patients in a population.

4. INNER JOIN

Question: Display PatientName and DiagnosisCode.

SELECT p.PatientName, d.DiagnosisCode
FROM Patients p
INNER JOIN Diagnoses d
ON p.PatientID = d.PatientID;

Join Column: PatientID

Healthcare Connection:
This joins the patient table with the diagnosis table so we can see which diagnoses belong to each patient.

5. GROUP BY

Question: Count how many patients have each DiagnosisCode.

SELECT DiagnosisCode, COUNT(DISTINCT PatientID) AS PatientCount
FROM Diagnoses
GROUP BY DiagnosisCode;

Healthcare Connection:
This is similar to counting how many patients have diabetes, hypertension, or chronic kidney disease.

6. LEFT JOIN + IS NULL

Question: Find patients who do not have any diagnosis recorded.

SELECT p.PatientName
FROM Patients p
LEFT JOIN Diagnoses d
ON p.PatientID = d.PatientID
WHERE d.PatientID IS NULL;

Mental Picture:
Start with every patient. Attach diagnosis records if they exist. Then keep only the patients where nothing was attached.

Healthcare Connection:
This can help find patients with missing documentation or no diagnosis records.

7. NOT EXISTS

Question: Find patients who do not have any diagnosis recorded using NOT EXISTS.

SELECT p.PatientName
FROM Patients p
WHERE NOT EXISTS (
    SELECT 1
    FROM Diagnoses d
    WHERE d.PatientID = p.PatientID
);

Mental Picture:
For each patient, ask: “Does a diagnosis exist?” If the answer is no, include that patient.

8. Scalar Subquery

Question: Find patients whose age is greater than the average age of all patients.

SELECT PatientName, Age
FROM Patients
WHERE Age > (
    SELECT AVG(Age)
    FROM Patients
);

Mental Picture:
First calculate the average age. Then compare each patient’s age to that average.

9. Correlated Subquery

Question: Return patients who have more than one diagnosis.

SELECT p.PatientName
FROM Patients p
WHERE (
    SELECT COUNT(*)
    FROM Diagnoses d
    WHERE d.PatientID = p.PatientID
) > 1;

Mental Picture:
The outer query looks at one patient at a time. The inner query counts diagnoses for that same patient. If the count is greater than 1, the patient is included.

Healthcare Connection:
This can help identify patients with multiple documented conditions.

LEFT JOIN + IS NULL vs. NOT EXISTS

LEFT JOIN + IS NULL

This method starts with all rows from the first table, tries to attach matching rows from the second table,
and then keeps only the rows where no match was found.

Simple idea:
“Show everyone first, then find who has nothing attached.”

NOT EXISTS

This method checks each row one at a time and asks whether a matching row exists in the second table.
If no matching row exists, the row is included.

Simple idea:
“For each patient, ask: does a matching diagnosis exist? If not, keep the patient.”

My Learning Note

  • SELECT retrieves columns from a table.
  • WHERE filters rows.
  • COUNT counts records.
  • INNER JOIN returns only matching records.
  • LEFT JOIN + IS NULL finds missing matches.
  • NOT EXISTS checks whether a matching record does not exist.
  • Scalar subquery returns one value, such as an average.
  • Correlated subquery runs based on each row from the outer query.

These SQL patterns are useful in healthcare analytics because they help answer real business questions,
such as finding patients with certain diagnoses, identifying missing documentation, counting conditions,
and comparing patients to population averages.

Scroll to Top