← CS50: Introduction to Computer Science

Week 7: SQL

Data organized into tables instead of files: querying, joining, and the two classic ways an attacker can break a database.

8 topics · 32 questions

Each topic is a path, not a menu: work down it in order and every question builds on the one above. Click any of them to watch the answer draw itself.

Relational Data and Tables

  1. How it worksHow does splitting data into separate tables of rows and columns avoid repeating the same information over and over?
  2. Common mistakeWhy isn't storing every actor's name directly inside the movies table itself a good design?
  3. Going furtherWhat happens to data consistency when the same actor's name is spelled two different ways across two rows?
  4. Ties togetherHow does a table's rows and columns connect back to a 2D array's rows and columns in C?
Your turn1 of 2
Storing an actor's name directly inside a movies table means repeating that name once for every movie they appear in. If an actor appears in 35 movies, how many times does their name get repeated across the table?

Primary and Foreign Keys

  1. How it worksHow does a foreign key in one table point at a primary key in another, linking the two tables together?
  2. Worked exampleWalk me through how a movie's id of 1442, used as a foreign key, connects a row in a stars table back to that movie.
  3. Common mistakeWhy isn't a foreign key required to be unique the way a primary key is?
Your turn1 of 2
A movie's id of 7267 is used as a foreign key in 13 separate rows of a stars table, one per actor in the movie. How many rows in the stars table reference that movie?

SELECT Queries

  1. How it worksHow does a SELECT statement pick out specific columns from a table before a WHERE clause filters the rows?
  2. How it worksHow does WHERE decide which rows survive a query based on a condition checked one row at a time?
  3. Worked exampleWalk me through a SQL query that selects the title from a movies table where the year equals 1994.
  4. Common mistakeWhy isn't SQL's SELECT·always the fastest way to get the data you actually need?
Your turn1 of 2
A movies table has 363 rows, and a SELECT with a WHERE clause matches 32 of them. How many rows does the query return?

INSERT, UPDATE, and DELETE

  1. Worked exampleWalk me through an UPDATE statement that changes a movie's rating to 8.5 where its id equals 1442.
  2. Common mistakeWhy doesn't running DELETE without a WHERE clause just delete one row by mistake?
  3. Going furtherWhat happens to a table's other rows when an UPDATE statement's WHERE clause matches more than one row?
Your turn1 of 2
An UPDATE statement's WHERE clause matches 70 rows in a table instead of the single row the programmer intended. How many extra rows besides the intended one get modified?

JOINs Across Tables

  1. How it worksHow does a JOIN slide two tables together, matching rows where a shared key lines up?
  2. How it worksHow does an inner JOIN drop any row from either table that has no match on the other side?
  3. Worked exampleWalk me through joining a movies table and a stars table on movie id to list every actor in the movie with id 1442.
  4. Common mistakeWhy doesn't a JOIN actually duplicate the data sitting inside either original table?
  5. Going furtherWhat happens to the result set when you JOIN three tables together instead of just two?
Your turn1 of 2
Joining a movies table of 21 rows with a stars table on movie id, where each movie has exactly 5 actors listed, produces how many result rows?

Indexes and Query Speed

  1. How it worksHow does an index let a database jump straight to matching rows instead of scanning the whole table?
  2. Common mistakeWhy isn't adding an index to every single column always a free performance win?
  3. Worked exampleFind roughly how a search's speed changes when an index turns a full scan of a million rows into a binary search.
  4. Going furtherWhat happens to a table's write speed once you've added indexes to five of its columns?
Your turn1 of 2
A table has n = 215 rows. An index turns a search into a binary search over those rows instead of a full scan. About how many comparisons does the indexed binary search need?

Race Conditions in Databases

  1. How it worksHow does a race condition happen when two requests read the same row before either one has written its update?
  2. Common mistakeWhy isn't checking that a ticket is available and then buying it, as two separate steps, actually safe?
  3. Going furtherWhat happens when a database wraps the check-then-buy steps inside one single atomic transaction instead?
  4. Ties togetherHow does a database race condition connect to two Scratch threads touching the same variable at the same time?
Your turn1 of 2
A ticket row shows 71 tickets available. Two requests both read that same value of 71 before either writes back an update, and both then subtract 1 and save. What ticket count ends up saved, instead of the correct value of 71 minus 2?

SQL Injection

  1. How it worksHow does an attacker sneak SQL syntax into a text field so the database executes it as a command instead of data?
  2. Worked exampleWalk me through how entering the text OR 1 equals 1 dash dash into a password field can trick a poorly written login query.
  3. Common mistakeWhy isn't checking that a text field is 'not empty' enough to protect against SQL injection?
  4. Going furtherWhat happens to the injection attack once a query uses parameterized placeholders instead of pasting text in directly?
  5. Ties togetherHow does SQL injection connect back to a buffer overflow: both exploit code that trusts input it should have checked?
Your turn1 of 2
An attacker's injected SQL text turns a query intended to match 4 legitimate rows into one that matches all 684 rows in the table. How many extra, unintended rows does the injected query expose?