Fixing Column and JOIN Mistakes in SQL (CS50 DESE – 8.sql)

[Rajeev Bagra]

While solving 8.sql in
CS50’s Introduction to Databases with SQL, we attempted:

SELECT name.districts, pupil.expenditures
FROM districts
JOIN expenditures
ON districts.id = expenditures.id;

This contains three separate SQL mistakes — all extremely common.

Let’s break them down clearly.

---

🚨 Mistake 1: Column Reference Syntax Is Backwards

You wrote:

name.districts
pupil.expenditures

But SQL syntax is:

table.column

NOT:

column.table

So:

❌ name.districts
✅ districts.name

❌ pupil.expenditures
✅ expenditures.pupils

Order matters.

---

🚨 Mistake 2: Wrong Column Name

In the schema:

From the expenditures table:

• district_id
• pupils
• per_pupil_expenditure

There is no column called:

pupil

It is:

pupils   (plural)

SQL is exact. One letter off = error.

---

🚨 Mistake 3: Incorrect JOIN Condition

You wrote:

ON districts.id = expenditures.id

This is incorrect.

Why?

Because:

• districts.id → district primary key
• expenditures.id → expenditure record ID (not district ID)

They are unrelated IDs.

The correct relationship is:

districts.id = expenditures.district_id

That’s the foreign key.

---

🧠 Understanding the Relationship

From the schema:

districts (id)
        ↓
expenditures (district_id)

That is the connection.

---

✅ Correct Query for 8.sql

SELECT districts.name, expenditures.pupils
FROM districts
JOIN expenditures
ON districts.id = expenditures.district_id
ORDER BY districts.name;

---

✨ Even Better (Using Aliases – Professional Style)

SELECT d.name, e.pupils
FROM districts d
JOIN expenditures e
ON d.id = e.district_id
ORDER BY d.name;

Cleaner. Shorter. Production-style SQL.

---

🎯 What This Question Is Really Teaching

This exercise tests whether you understand:

✔ Table.column syntax

✔ Foreign key relationships

✔ Exact column names

✔ Correct JOIN logic

It is not about complex SQL.

It is about relational clarity.

---

📌 The Big SQL Lesson

Before writing a JOIN, always ask:

1. What is the primary key?
2. What is the foreign key?
3. Are these columns logically related?

If you JOIN on unrelated IDs, you create meaningless data.

---

 

[Author]

Posts