SQL Concepts Using Hospital Data

Introduction

Hospitals collect data about patients, visits, doctors, treatments and bills. This data can help answer practical questions: How many times has a patient visited? How does their latest bill compare with the previous one? Which patients returned on another date?

SQL provides several ways to answer these questions. In this article, we explore four important concepts: window functions, joins, common table expressions (CTEs) and subqueries using our hospital dataset.

Each concept is explained in simple language, followed by a hospital scenario, a SQL query and its expected result. The aim is to understand what each query does, why it is useful and when to use it.

About the dataset

The hospital dataset contains 30 visits and 25 patients. Each row in hospital_visits represents a visit. We use columns such as visit_id, patient_id, visit_date and total_bill_kes to answer the questions below. Source dates use day/month/year, and billed amounts are in Kenyan shillings (KES).

The examples use PostgreSQL syntax. Import the CSV into a table named hospital_visits, or use the companion SQL script, which includes the data and the queries. Query outputs were checked using DuckDB and are shown below as result tables.

Before we begin:

• AS gives a result column a readable name.
• WHERE chooses rows.
• ORDER BY sorts rows.
• NULL means no value is available; it does not mean zero.

Billed amounts do not necessarily represent payments received.

1. Window Functions

A window function calculates across related rows while keeping each visit visible. This is useful when we want the individual bill and a comparison beside it.

By comparison, GROUP BY normally produces one summary row per group.

Understanding OVER, PARTITION BY and ORDER BY

These clauses control how a window function works:

• OVER() tells SQL to use a window. Empty parentheses use all rows available to the query.
• PARTITION BY divides those rows into groups, such as patients.
• ORDER BY inside OVER() sets the calculation sequence.
• A window frame chooses which rows in the group to include.

AVG — Calculate the Average Bill

AVG() calculates the average.

Hospital scenario: Compare each of Amina’s bills with her average bill. PARTITION BY patient_id makes a separate calculation for each patient. [1, 3]

SQL query:

SELECT visit_id, total_bill_kes,
       AVG(total_bill_kes) OVER (
           PARTITION BY patient_id
       ) AS average_bill
FROM hospital_visits
WHERE patient_id = 1
ORDER BY visit_id;

Result:

visit_id	total_bill_kes	average_bill
1001	3,500	3,850
1011	4,200	3,850

Explanation: Both visits show an average of KES 3,850 because:

(3,500 + 4,200) / 2 = 3,850

Without PARTITION BY, OVER() would average all rows kept by WHERE. In this example, that is still Amina’s two visits.

ROW_NUMBER — Number Each Patient’s Visits

ROW_NUMBER() gives each row a different number.

Hospital scenario: Identify a patient’s first and second recorded visits. The numbering restarts for each patient.

SQL query:

SELECT patient_id, visit_id,
       ROW_NUMBER() OVER (
           PARTITION BY patient_id
           ORDER BY visit_date, visit_id
       ) AS visit_number
FROM hospital_visits
WHERE patient_id IN (1, 2)
ORDER BY patient_id, visit_number;

Result:

patient_id	visit_id	visit_number
1	1001	1
1	1011	2
2	1002	1
2	1021	2

Explanation: Patients 1 and 2 each have visit numbers 1 and 2.

Inside the window, visit_date sets the sequence and visit_id breaks a tie if two visits share a date. The final ORDER BY arranges the displayed result.

RANK and DENSE_RANK — Compare Bills

RANK() gives tied bills the same position and skips positions afterwards.

DENSE_RANK() also gives tied bills the same position, but leaves no gaps.

Hospital scenario: Rank bills in Orthopedics where two visits have the same billed amount.

SQL query:

SELECT visit_id, total_bill_kes,
       RANK() OVER (
           ORDER BY total_bill_kes
       ) AS rank_no,
       DENSE_RANK() OVER (
           ORDER BY total_bill_kes
       ) AS dense_no
FROM hospital_visits
WHERE department_id = 30
ORDER BY total_bill_kes, visit_id;

Result:

visit_id	total_bill_kes	rank_no	dense_no
1008	6,500	1	1
1020	6,500	1	1
1023	7,200	3	2
1015	24,000	4	3
1003	28,000	5	4
1027	33,000	6	5

Explanation: The two KES 6,500 bills both rank 1.

The KES 7,200 bill receives:

• RANK() = 3, because two rows occupy the first position.
• DENSE_RANK() = 2, because it continues with the next position.

Unlike these functions, ROW_NUMBER() would give the tied visits different numbers.

NTILE — Divide Bills into Groups

NTILE() divides ordered rows into groups of nearly equal size.

Hospital scenario: Divide the Orthopedics bills into two groups for review. NTILE(2) creates two groups.

SQL query:

SELECT visit_id, total_bill_kes,
       NTILE(2) OVER (
           ORDER BY total_bill_kes, visit_id
       ) AS bill_group
FROM hospital_visits
WHERE department_id = 30
ORDER BY total_bill_kes, visit_id;

Result:

visit_id	total_bill_kes	bill_group
1008	6,500	1
1020	6,500	1
1023	7,200	1
1015	24,000	2
1003	28,000	2
1027	33,000	2

Explanation: The three lower bills are in group 1 and the three higher bills are in group 2.

The groups contain equal numbers of visits, not equal billing totals. NTILE() can split tied values between groups.

LAG and LEAD — Compare Nearby Visits

LAG() looks backward to the previous row.

LEAD() looks forward to the next row.

Hospital scenario: Compare Amina’s current bill with the bills from her previous and next recorded visits.

SQL query:

SELECT visit_id, total_bill_kes,
       LAG(total_bill_kes) OVER (
           ORDER BY visit_date, visit_id
       ) AS previous_bill,
       LEAD(total_bill_kes) OVER (
           ORDER BY visit_date, visit_id
       ) AS next_bill
FROM hospital_visits
WHERE patient_id = 1
ORDER BY visit_date, visit_id;

Result:

visit_id	total_bill_kes	previous_bill	next_bill
1001	3,500	NULL	4,200
1011	4,200	3,500	NULL

Explanation:

• Visit 1001 has next_bill of KES 4,200.
• Visit 1011 has previous_bill of KES 3,500.
• NULL appears where no neighbouring visit exists.

Only patient 1 is selected here. When analysing several patients, add PARTITION BY patient_id to both windows so that each patient is considered separately.

FIRST_VALUE and LAST_VALUE — Compare First and Latest Bills

FIRST_VALUE() reads the first value in the window frame.

LAST_VALUE() reads the last value in the window frame.

Hospital scenario: Display Amina’s earliest and latest recorded bills beside both of her visits.

SQL query:

SELECT visit_id,
       FIRST_VALUE(total_bill_kes) OVER (
           ORDER BY visit_date, visit_id
       ) AS first_bill,
       LAST_VALUE(total_bill_kes) OVER (
           ORDER BY visit_date, visit_id
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING
       ) AS last_bill
FROM hospital_visits
WHERE patient_id = 1
ORDER BY visit_id;

Result:

visit_id	first_bill	last_bill
1001	3,500	4,200
1011	3,500	4,200

Explanation: Both rows show:

• First bill: KES 3,500.
• Latest bill: KES 4,200.

UNBOUNDED FOLLOWING extends the frame to the final row. Without it, LAST_VALUE() often returns the current row’s value rather than the latest bill.

SUM — Calculate a Patient’s Total Bill

SUM() adds values.

Hospital scenario: Display Amina’s total billed amount beside each visit while keeping both individual visits visible.

SQL query:

SELECT visit_id, total_bill_kes,
       SUM(total_bill_kes) OVER () AS total_bill
FROM hospital_visits
WHERE patient_id = 1
ORDER BY visit_id;

Result:

visit_id	total_bill_kes	total_bill
1001	3,500	7,700
1011	4,200	7,700

Explanation: Both rows show KES 7,700 because:

3,500 + 4,200 = 7,700

OVER() uses both rows selected by WHERE.

COUNT — Count a Patient’s Visits

COUNT(*) counts rows.

Hospital scenario: Show how many recorded visits Amina has beside each visit.

SQL query:

SELECT visit_id,
       COUNT(*) OVER () AS number_of_visits
FROM hospital_visits
WHERE patient_id = 1
ORDER BY visit_id;

Result:

visit_id	number_of_visits
1001	2
1011	2

Explanation: The count is 2 beside each of Amina’s visits.

COUNT(*) counts rows, while COUNT(column) counts only non-NULL values in that column.

MIN and MAX — Find the Lowest and Highest Bills

MIN() returns the smallest value.

MAX() returns the largest value.

Hospital scenario: Identify the lowest and highest bills recorded for Amina.

SQL query:

SELECT visit_id,
       MIN(total_bill_kes) OVER () AS lowest_bill,
       MAX(total_bill_kes) OVER () AS highest_bill
FROM hospital_visits
WHERE patient_id = 1
ORDER BY visit_id;

Result:

visit_id	lowest_bill	highest_bill
1001	3,500	4,200
1011	3,500	4,200

Explanation: Both rows show a lowest bill of KES 3,500 and a highest bill of KES 4,200.

SUM(), AVG(), MIN() and MAX() ignore NULL inputs.

Window Frames — Calculate a Running Total

A window frame tells SQL which rows to include for the current calculation.

Hospital scenario: Follow how Amina’s total billed amount grows with each visit. [1, 4]

SQL query:

SELECT visit_id, total_bill_kes,
       SUM(total_bill_kes) OVER (
           ORDER BY visit_date, visit_id
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
       ) AS running_total
FROM hospital_visits
WHERE patient_id = 1
ORDER BY visit_date, visit_id;

Result:

visit_id	total_bill_kes	running_total
1001	3,500	3,500
1011	4,200	7,700

Explanation: The total grows from KES 3,500 to KES 7,700.

The frame means:

• ROWS: use row positions.
• UNBOUNDED PRECEDING: start at the first row.
• CURRENT ROW: stop at the current row.

Unlike the earlier SUM() example, the running total changes as SQL moves forward.

Other useful frame options include:

• ROWS BETWEEN 1 PRECEDING AND CURRENT ROW: include the previous and current rows, useful for a moving average.
• UNBOUNDED FOLLOWING: extend to the final row.
• RANGE: treat equal ORDER BY values as peers.
• GROUPS: count peer groups.

With ORDER BY, the default frame runs from the beginning through the current row’s peers. Without ORDER BY, it covers the whole partition. [1, 4]

2. SQL Joins

A join combines related rows.

We will compare patients seen on 1 June with those seen on 11 June:

• june_1 contains patient IDs 1 and 2.
• june_11 contains patient IDs 2 and 20.

These groups come directly from the hospital dataset.

Prepare the Helper Views

A view is a saved query that we can read like a table.

Run this setup once after importing the CSV into hospital_visits. If you already ran the companion script, these views already exist.

The first two views give us distinct patient and department records. The last two select visits on the dates we want to compare.

SQL query:

CREATE VIEW patients AS
SELECT DISTINCT patient_id, patient_name
FROM hospital_visits;

CREATE VIEW departments AS
SELECT DISTINCT department_id, department_name
FROM hospital_visits;

CREATE VIEW june_1 AS
SELECT patient_id
FROM hospital_visits
WHERE visit_date = DATE '2026-06-01';

CREATE VIEW june_11 AS
SELECT patient_id
FROM hospital_visits
WHERE visit_date = DATE '2026-06-11';

DISTINCT removes repeated ID and name pairs.

The two date groups:

Visit date	Patient IDs
1 June 2026	1, 2
11 June 2026	2, 20

In the queries below:

• a and b are short names for the two views.
• ON tells SQL how to match their rows.

INNER JOIN — Keep Matches Only

An INNER JOIN keeps rows that match on both sides.

Hospital scenario: Identify patients who attended on both dates. [5]

SQL query:

SELECT a.patient_id AS june_1_id,
       b.patient_id AS june_11_id
FROM june_1 AS a
INNER JOIN june_11 AS b
    ON a.patient_id = b.patient_id
ORDER BY a.patient_id;

Result:

june_1_id	june_11_id
2	2

Explanation: Only patient 2 appears because this ID exists in both groups.

LEFT JOIN — Keep Every Patient in the First Group

A LEFT JOIN keeps every row from the left group and adds matching rows from the right group.

Hospital scenario: Check whether each patient seen on 1 June returned on 11 June.

SQL query:

SELECT a.patient_id AS june_1_id,
       b.patient_id AS june_11_id
FROM june_1 AS a
LEFT JOIN june_11 AS b
    ON a.patient_id = b.patient_id
ORDER BY a.patient_id;

Result:

june_1_id	june_11_id
1	NULL
2	2

Explanation: Patients 1 and 2 remain.

Patient 1 has NULL on the right because no matching 11 June visit exists in that group.

RIGHT JOIN — Keep Every Patient in the Second Group

A RIGHT JOIN keeps every row from the right group.

Hospital scenario: List all patients seen on 11 June, including those absent from the 1 June group.

SQL query:

SELECT a.patient_id AS june_1_id,
       b.patient_id AS june_11_id
FROM june_1 AS a
RIGHT JOIN june_11 AS b
    ON a.patient_id = b.patient_id
ORDER BY b.patient_id;

Result:

june_1_id	june_11_id
2	2
NULL	20

Explanation: Patient 2 matches both groups. Patient 20 has NULL on the left because no 1 June match exists.

A RIGHT JOIN can be rewritten as a LEFT JOIN by reversing the table order.

FULL OUTER JOIN — Keep Both Groups

A FULL OUTER JOIN keeps matching and unmatched rows from both sides.

Hospital scenario: Compare attendance across both dates without dropping patients from either group.

SQL query:

SELECT a.patient_id AS june_1_id,
       b.patient_id AS june_11_id
FROM june_1 AS a
FULL OUTER JOIN june_11 AS b
    ON a.patient_id = b.patient_id
ORDER BY COALESCE(a.patient_id, b.patient_id);

Result:

june_1_id	june_11_id
1	NULL
2	2
NULL	20

Explanation:

• Patient 1 appears only in the first group.
• Patient 2 appears in both groups.
• Patient 20 appears only in the second group.

COALESCE() selects the available non-NULL ID to sort the rows.

CROSS JOIN — Create Every Possible Pair

A CROSS JOIN pairs every row from one group with every row from another.

Hospital scenario: Create a grid of possible comparisons between the two attendance groups.

SQL query:

SELECT a.patient_id AS june_1_id,
       b.patient_id AS june_11_id
FROM june_1 AS a
CROSS JOIN june_11 AS b
ORDER BY a.patient_id, b.patient_id;

Result:

june_1_id	june_11_id
1	2
1	20
2	2
2	20

Explanation: Two rows multiplied by two rows gives four combinations.

These pairs are possible combinations, not evidence of a relationship between patients.

SELF JOIN — Connect Repeat Visits

A SELF JOIN uses the same table twice.

Hospital scenario: Place two visits by the same patient side by side. Here we use patient 1.

SQL query:

SELECT a.patient_id,
       a.visit_id AS first_visit,
       b.visit_id AS later_visit
FROM hospital_visits AS a
JOIN hospital_visits AS b
    ON a.patient_id = b.patient_id
   AND a.visit_date < b.visit_date
WHERE a.patient_id = 1;

Result:

patient_id	first_visit	later_visit
1	1001	1011

Explanation: Patient 1’s visit 1001 is paired with visit 1011.

a and b represent two uses of the same table. The earlier-date condition avoids pairing a visit with itself or returning the reversed pair.

Comparing the Joins

Join	What it keeps
INNER JOIN	Matching rows from both sides
LEFT JOIN	Every left row and its right matches
RIGHT JOIN	Every right row and its left matches
FULL OUTER JOIN	Matching and unmatched rows from both sides
CROSS JOIN	Every possible combination
SELF JOIN	Related rows from the same table

A join can produce several rows for one patient when several records match. Always check the matching key and the number of rows returned.

3. Common Table Expressions

A common table expression, or CTE, names a query result using WITH.

The name lasts for one SQL statement. It helps us break a question into smaller steps without creating a permanent table.

A Basic CTE — Find High Bills

A basic CTE gives one query result a temporary name.

Hospital scenario: Prepare a list of visits with bills above KES 40,000.

SQL query:

WITH high_bills AS (
    SELECT visit_id, total_bill_kes
    FROM hospital_visits
    WHERE total_bill_kes > 40000
)
SELECT *
FROM high_bills
ORDER BY total_bill_kes DESC;

Result:

visit_id	total_bill_kes
1017	82,000
1010	76,000
1004	42,000

Explanation: The CTE creates a list named high_bills. The final query reads that list.

Three visits qualify. SELECT * displays all columns from the CTE.

Multiple CTEs — Build a Two-Step Report

We can define several CTEs by separating them with commas.

Hospital scenario: Calculate each patient’s total bill, then select patients whose total exceeds KES 90,000.

SQL query:

WITH patient_totals AS (
    SELECT patient_id,
           SUM(total_bill_kes) AS total_bill
    FROM hospital_visits
    GROUP BY patient_id
), high_totals AS (
    SELECT *
    FROM patient_totals
    WHERE total_bill > 90000
)
SELECT *
FROM high_totals;

Result:

patient_id	total_bill
10	98,000

Explanation:

1. patient_totals calculates one total per patient.
2. high_totals selects totals above KES 90,000.
3. The final query displays the qualifying patient.

Only patient 10 qualifies, with KES 98,000 across two visits.

A CTE with a Join and Aggregation

CTEs can combine calculations such as COUNT() with joins.

Hospital scenario: Count each patient’s visits then add patient names to create a repeat-patient list.

SQL query:

WITH visit_counts AS (
    SELECT patient_id,
           COUNT(*) AS visits
    FROM hospital_visits
    GROUP BY patient_id
)
SELECT p.patient_name, c.visits
FROM patients AS p
JOIN visit_counts AS c
    ON p.patient_id = c.patient_id
WHERE c.visits > 1
ORDER BY p.patient_id;

Result:

patient_name	visits
Amina Otieno	2
John Kariuki	2
Esther Achieng	2
Lucy Wambui	2
Peter Ouma	2

Explanation: The CTE counts visits for each patient. The join adds the patient’s name from the patients view.

Five patients have two recorded visits each.

A Recursive CTE — Generate Reporting Dates

A recursive CTE uses its own previous result to continue a sequence.

Hospital scenario: Build a three-day reporting calendar starting from the date of visit 1001.

SQL query:

WITH RECURSIVE calendar(day) AS (
    SELECT visit_date
    FROM hospital_visits
    WHERE visit_id = 1001

    UNION ALL

    SELECT day + 1
    FROM calendar
    WHERE day < DATE '2026-06-03'
)
SELECT day
FROM calendar
ORDER BY day;

Result:

day
2026-06-01
2026-06-02
2026-06-03

Explanation:

1. The first SELECT starts with 1 June.
2. The second SELECT adds one day to the previous result.
3. WHERE stops the sequence at 3 June.
4. UNION ALL appends the generated rows.

The stopping condition is essential so that the query does not continue indefinitely.

CTEs Compared with Subqueries

A CTE places a named step before the main query. A subquery places a SELECT inside another query.

CTE	Subquery
Defined using WITH	Written inside another query
Has a name	Often used directly where needed
Can be referenced several times within the statement	Useful for a short, local calculation
Can support recursion	An ordinary subquery does not refer to itself

Choose a CTE for several readable steps or reuse within one statement. Choose a subquery for a short calculation.

Neither approach is automatically faster.

4. SQL Subqueries

A subquery is a query inside another query.

A subquery can:

• Supply a value in SELECT.
• Provide a small table in FROM.
• Filter rows in WHERE.

The categories can overlap. For example, a scalar subquery may also be correlated.

A Scalar Subquery in SELECT

A scalar subquery supplies a single value.

Hospital scenario: Display the highest hospital bill next to Amina’s individual bills.

SQL query:

SELECT visit_id, total_bill_kes,
       (
           SELECT MAX(total_bill_kes)
           FROM hospital_visits
       ) AS highest_hospital_bill
FROM hospital_visits
WHERE patient_id = 1
ORDER BY visit_id;

Result:

visit_id	total_bill_kes	highest_hospital_bill
1001	3,500	82,000
1011	4,200	82,000

Explanation: The inner query finds the highest bill across the hospital: KES 82,000. The outer query displays it beside both of Amina’s bills.

A scalar subquery must return one column and at most one row:

• More than one row causes an error.
• No rows produce NULL.

A Non-Correlated Subquery in WHERE

A non-correlated subquery does not refer to the outer query. It can be understood independently.

Hospital scenario: Find bills above the hospital’s average bill.

SQL query:

SELECT visit_id, total_bill_kes
FROM hospital_visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM hospital_visits
)
ORDER BY total_bill_kes DESC, visit_id
LIMIT 3;

Result:

visit_id	total_bill_kes
1017	82,000
1010	76,000
1004	42,000

Explanation: The inner query calculates an average of about KES 16,266.67.

Ten visits exceed that amount. LIMIT 3 displays only the three highest qualifying bills.

Every bill is compared with the same hospital average.

A Correlated Subquery — Count Each Patient’s Visits

A correlated subquery uses a value from the outer query.

Hospital scenario: Count visits separately for each selected patient.

SQL query:

SELECT p.patient_id, p.patient_name,
       (
           SELECT COUNT(*)
           FROM hospital_visits AS v
           WHERE v.patient_id = p.patient_id
       ) AS visits
FROM patients AS p
WHERE p.patient_id IN (1, 3)
ORDER BY p.patient_id;

Result:

patient_id	patient_name	visits
1	Amina Otieno	2
3	Grace Naliaka	1

Explanation: Amina has two visits and Grace has one.

p.patient_id connects the inner count to the current patient in the outer query. Unlike the previous example, the answer depends on which patient is being considered.

Nested Subqueries — Answer a Question in Stages

A nested subquery contains another subquery.

Hospital scenario: Find bills across the hospital that exceed the average bill in Cardiology.

SQL query:

SELECT visit_id, total_bill_kes
FROM hospital_visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM hospital_visits
    WHERE department_id IN (
        SELECT department_id
        FROM departments
        WHERE department_name = 'Cardiology'
    )
)
ORDER BY total_bill_kes DESC, visit_id;

Result — first 5 of 8 rows:

visit_id	total_bill_kes
1017	82,000
1010	76,000
1004	42,000
1016	36,000
1027	33,000

Explanation:

1. The innermost query finds Cardiology’s department ID: 10.
2. The middle query calculates Cardiology’s average bill: about KES 23,344.44.
3. The outer query finds hospital bills above that amount.

Eight bills qualify. The table displays the first five.

IN — Find Emergency Patients

IN checks whether a value belongs to a list returned by a subquery.

Hospital scenario: List patients who have a recorded Emergency visit.

SQL query:

SELECT patient_id, patient_name
FROM patients
WHERE patient_id IN (
    SELECT patient_id
    FROM hospital_visits
    WHERE admission_type = 'Emergency'
);

Result:

patient_id	patient_name
10	Peter Ouma

Explanation: The inner query identifies patient IDs with an Emergency visit. The outer query retrieves their names.

Only patient 10, Peter Ouma, qualifies.

EXISTS — Check Whether a Matching Visit Exists

EXISTS checks whether a subquery finds at least one row.

Hospital scenario: Check whether each patient has a recorded Emergency visit.

SQL query:

SELECT p.patient_id, p.patient_name
FROM patients AS p
WHERE EXISTS (
    SELECT 1
    FROM hospital_visits AS v
    WHERE v.patient_id = p.patient_id
      AND v.admission_type = 'Emergency'
);

Result:

patient_id	patient_name
10	Peter Ouma

Explanation: This returns the same patient as the IN example.

The difference is how we express the question:

• IN: Is the patient’s ID in the returned list?
• EXISTS: Does a matching Emergency visit exist?

SELECT 1 indicates that we only care whether a row is found.

ANY — Exceed at Least One Comparison Bill

ANY means the comparison must succeed for at least one returned value.

Hospital scenario: Find bills that exceed at least one of Amina’s bills: KES 3,500 and KES 4,200.

SQL query:

SELECT visit_id, total_bill_kes
FROM hospital_visits
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM hospital_visits
    WHERE patient_id = 1
)
ORDER BY total_bill_kes, visit_id
LIMIT 3;

Result:

visit_id	total_bill_kes
1021	3,800
1005	4,000
1011	4,200

Explanation: A qualifying bill only needs to exceed one comparison value.

For example, KES 4,000 qualifies because it exceeds KES 3,500, even though it is below KES 4,200.

The table shows the first three qualifying bills.

ALL — Exceed Every Comparison Bill

ALL means the comparison must succeed for every returned value.

Hospital scenario: Find bills that exceed both of Amina’s bills.

SQL query:

SELECT visit_id, total_bill_kes
FROM hospital_visits
WHERE total_bill_kes > ALL (
    SELECT total_bill_kes
    FROM hospital_visits
    WHERE patient_id = 1
)
ORDER BY total_bill_kes, visit_id
LIMIT 3;

Result:

visit_id	total_bill_kes
1029	4,800
1019	5,200
1007	5,500

Explanation: These bills exceed both KES 3,500 and KES 4,200.

Unlike ANY, a bill of KES 4,000 fails because it does not exceed KES 4,200.

These comparison values contain no NULLs. NULL inputs require care. Also, ANY is false for an empty comparison set, while ALL is true.

A Subquery in FROM — Summarise Patient Bills

A subquery in FROM acts like a small table for the outer query.

Hospital scenario: Calculate patient totals, then display totals above KES 90,000.

SQL query:

SELECT patient_id, total_bill
FROM (
    SELECT patient_id,
           SUM(total_bill_kes) AS total_bill
    FROM hospital_visits
    GROUP BY patient_id
) AS totals
WHERE total_bill > 90000;

Result:

patient_id	total_bill
10	98,000

Explanation: The inner query creates a table of patient totals. totals is the name given to that result.

The outer query selects totals above KES 90,000. Patient 10 qualifies with KES 98,000.

This answers the same question as the multiple-CTE example, using a different layout.

Conclusion

Using the hospital dataset, we have seen how SQL can help us compare bills, follow a patient’s visits and identify returning patients.

Window functions calculate totals, rankings and comparisons while keeping individual visits visible. Joins connect related records. CTEs organise queries into clear, named steps, while subqueries use the result of one query within another.

The best starting point is a clear question. Decide which records you need, choose the SQL approach that fits, and check whether the result answers your question. Practising with small examples makes these concepts easier to understand and apply to larger datasets.