CS50 Week 7 Explained: SQL and Relational Databases

Week 7 introduces a new kind of language. In C and Python you tell the computer how to do something, step by step. In SQL you describe what you want ("every show with a rating above 8, sorted by year") and the database works out how to get it. It's also one of the most employable skills in the course: nearly every business runs on databases.

🎓

An unofficial study guide. CS50 is Harvard University's Introduction to Computer Science, taught by David J. Malan. The lectures, notes and problem sets linked here belong to Harvard and are shared under CC BY-NC-SA 4.0. This guide is Cocoon's own writing and is not affiliated with or endorsed by Harvard. Take the course itself, free, at cs50.harvard.edu/x. Part of our CS50 study series. Keep track of your progress with our free CS50 Study Tracker.

CS50 Week 7 Explained: SQL and Relational Databases, pixel-art illustration — CS50 Week 7 Explained: SQL and Relational Databases

Watch the lecture

CS50x 2026, Lecture 7: SQL

2 h 16 min · Harvard University · CS50 on YouTube

Watch on YouTube ↗Week page ↗Harvard's notes ↗

Jump to the key moments

0:43Flat-file databases
24:10Relational databases
59:45Schema
1:06:05Primary and foreign keys
1:06:58Querying
1:40:47Indexes
1:45:56Python and SQL
1:57:46SQL injection attacks
2:06:15Race conditions

From CSV files to relational databases

The lecture starts with a flat-file database, a CSV file of the audience's favourite languages and problems, analysed with Python. It works, but every question needs a new program, and the data is full of inconsistencies. A relational database stores data in tables with defined columns and types, and you query it with SQL. CS50 uses SQLite, where the whole database is a single file.

The four operations

Almost everything is one of these: Create (INSERT), Read (SELECT), Update (UPDATE) and Delete (DELETE), known as CRUD. Most of your time goes into SELECT, refined with WHERE (filter), LIKE (pattern match), ORDER BY, LIMIT, GROUP BY, and functions like COUNT, AVG and DISTINCT.

Schemas and keys

The lecture moves to a large IMDb-derived database of TV shows. Instead of cramming everything into one table, data is split into related tables: shows, people, stars, genres, ratings. Each row has a primary key (a unique ID), and tables refer to each other with foreign keys. To bring related rows back together, you either nest one query inside another or JOIN tables on their keys. Once you can picture how the tables connect, most queries become straightforward.

Indexes

Searching a column with a million rows means scanning every row, which is O(n) again. An index builds a tree-shaped structure (a B-tree, a cousin of Week 5's trees) so lookups become far faster, at the cost of extra storage and slightly slower inserts. The lecture shows a query dropping from a noticeable pause to near-instant.

SQL from Python, and two dangers

Programs usually run SQL from another language, and the lecture uses Python with CS50's library. That brings two classic problems. SQL injection: if you glue user input straight into a query string, a user can type SQL that runs against your database. The fix is to always use ? placeholders and let the library fill them in safely. Race conditions: two requests read and update the same value at the same moment and one update is lost. The fix is a transaction that runs the steps together as one.

Problem Set 7: what it asks

Songs: write queries against a database of the most-streamed Spotify songs from 2018, then reflect on what the data says about a listener.
Movies: a series of increasingly hard queries against an IMDb-based movie database. Note the 2026 spec changes Harvard lists at the top of the page.
Fiftyville: the CS50 duck has been stolen. Use SQL on the town's records to find the thief, where they fled and who helped them.

Where people get stuck

Not looking at the schema. Run .schema first and sketch how the tables connect before writing a single query.
Writing a huge query in one go. Build it in layers. Get the innermost query working and check its output, then wrap it.
Movies' row counts: the spec tells you how many rows some queries should return. Use that as a free test.
Fiftyville: keep a log of every query and what you learned, as the problem asks. Detective work means narrowing the suspects step by step, so follow each clue into the next table.

Check yourself

What's the difference between a primary key and a foreign key?

A primary key uniquely identifies a row in its own table. A foreign key is a column that holds another table's primary key, which links the two.

Why is "SELECT * FROM users WHERE name = '" + name + "'" dangerous?

A user can type input that closes the quote and adds their own SQL. That's SQL injection. Use placeholders instead.

What does an index cost you?

Extra storage, and slightly slower inserts and updates, because the index has to be kept up to date too.

SQL is described as declarative. What does that mean?

You describe the result you want, not the steps to compute it. The database decides how to run the query.

Databases are how AI tools look things up. See it happen with a live job feed in the API Lab.

Try the API Lab →