Debugging a SQL Subquery: Finding the District with the Fewest Pupils (CS50 DESE)

[Rajeev Bagra]

 

While working through the DESE database exercises in
CS50’s Introduction to Databases with SQL, one task asks us to identify:

The school district(s) with the least number of pupils, reporting only their names.

This corresponds to 9.sql in the assignment.

During the process, the following query was attempted:

SELECT districts.name
FROM districts
WHERE expenditures.name =
(SELECT MIN(expenditures.pupils)
FROM expenditures);

At first glance, the idea behind the query is reasonable: use a subquery with MIN() to find the smallest number of pupils. However, the query contains several issues.

Let’s examine them and correct them step-by-step.

---

🎯 Understanding the Schema

From the DESE database structure:

districts table

column	meaning
id	district identifier
name	district name

expenditures table

column	meaning
district_id	district reference
pupils	number of pupils in the district
per_pupil_expenditure	spending per student

Relationship:

districts.id → expenditures.district_id

This tells us that the number of pupils belongs to the expenditures table, not the districts table.

---

🚨 Issue 1: Referencing a Table That Isn’t in the Query

The attempted query used:

WHERE expenditures.name

But the expenditures table was never included in the FROM clause.

SQL therefore has no idea where that table comes from.

Whenever you reference columns from another table, you must JOIN that table first.

---

🚨 Issue 2: Using a Column That Doesn’t Exist

The query referenced:

expenditures.name

But the expenditures table has no name column.

The district name is stored in:

districts.name

---

🚨 Issue 3: Comparing Incompatible Values

The attempted comparison was essentially:

district name = minimum pupils

This compares:

text = number

which makes no logical sense.

Instead we must compare:

pupil count = minimum pupil count

---

🧠 Correct Logical Approach

To answer the question properly, we need to:

1. Join districts with expenditures
2. Determine the smallest pupil count
3. Return the district(s) whose pupil count matches that value

---

✅ Correct Query

SELECT districts.name
FROM districts
JOIN expenditures
ON districts.id = expenditures.district_id
WHERE expenditures.pupils =
(
    SELECT MIN(pupils)
    FROM expenditures
);

---

🔎 How the Query Works

Step 1: Find the Minimum

SELECT MIN(pupils) FROM expenditures;

Example result:

230

---

Step 2: Match Districts with That Value

The outer query then finds all districts whose pupil count equals that minimum.

WHERE expenditures.pupils = 230

This returns the district name(s).

---

📊 Logical Flow

Find smallest pupil count
        ↓
Match districts with that value
        ↓
Return district name(s)

---

🚀 Key SQL Concept Learned

This exercise introduces a powerful SQL pattern:

WHERE column = (SELECT MIN(column))

This pattern is commonly used to find:

• the cheapest product
• the smallest city by population
• the lowest-performing branch
• the minimum sales value

---

📌 Final Takeaway

When a problem asks for rows with the smallest or largest value, using a subquery with MIN() or MAX() is often the safest and most accurate approach.

It ensures that all matching rows are returned, even when multiple records share the same minimum value.

---

 

[Author]

Posts