The labs on this site need JavaScript to run. The same content is written out below.

Learn

Database

Table design and SQL, indexes and execution plans, transactions and locking, replication and distribution. See how data is stored and read.

Relational model and design

• Tables, keys, and constraints — What data gets in when there are no constraints?
• ER modeling — How do you turn the relationships between entities into tables?
• Normalization — What happens if you do not split the table?

SQL

• SELECT processing order — Why can't you use an alias or an aggregate function in WHERE?
• JOIN and GROUP BY — Do you put the condition in ON or in WHERE?
• Subqueries — Why does NOT IN return no rows at all?
• Window functions — How do you add a rank and a running total without reducing the rows?
• Offset and keyset pagination — Why later pages get slower
• Views, stored procedures, and triggers — Why did the stock go down when you ran one INSERT line?

Indexes and execution plans

• Binary search and B-trees — If the data is sorted, you can discard half at a time
• Indexes and full table scans — Why is the index not used even though it exists?
• Composite and covering indexes — A composite index can be used only in column order
• Reading the execution plan — What should you read in one line of EXPLAIN?
• Join algorithms — How does the database find matching rows when the join column has no index?

Transactions and locking

• Transaction basics — What if the withdrawal went through but the deposit did not?
• Isolation levels — How does data that another transaction is changing look to me?
• Gap locks and next-key locks — Why is an INSERT blocked when I queried a row that does not exist?
• Stock decrement and locks — What if many orders arrive at once for 3 items in stock?
• Deadlocks in the database — When two transactions wait for each other, how does the database end it?

Storage and recovery

• Undo and redo logs — Why does the data survive even if the power goes out right after a commit?
• Buffer pool and pages — Why does reading one row read a whole 16KB page?

Scaling and distribution

• Replication lag and CAP — I just saved it, but the query shows the old value
• Sharding — What happens when you split data across several databases
• Partitioning — When a table is cut into pieces, which pieces does a query read?
• Redis, a key-value store — If one command is slow, everyone waits
• NoSQL and the document model — Split an order into three tables, or put it in one document?