codeloopback
Guide contents

Databases and SQL

Learn SQL, schema design, indexes, transactions, and query tuning with tested PostgreSQL examples and interview questions.

30 lessons in 7 sections

Start with Introduction: How This Course Works →

Getting Started

How the course works and the sample database used in every lesson.

  1. 1.Introduction: How This Course Works
  2. 2.The Sample Database

SQL Fundamentals

Querying, filtering, joins, subqueries, window functions, and changing data.

  1. 3.SELECT, WHERE, ORDER BY, and LIMIT
  2. 4.NULL and Three-Valued Logic
  3. 5.Aggregation, GROUP BY, and HAVING
  4. 6.Joins
  5. 7.Subqueries and EXISTS
  6. 8.Set Operations, CTEs, and Recursion
  7. 9.Window Functions
  8. 10.Functions and Expressions
  9. 11.Changing Data

Schema Design

Data types, constraints, keys, and normalization.

  1. 12.Data Types and Constraints
  2. 13.Normalization
  3. 14.Views and Routines

Indexes and Performance

How indexes work, reading query plans, and fixing slow queries.

  1. 15.How Indexes Work
  2. 16.Query Plans
  3. 17.Fixing Slow Queries

Transactions and Concurrency

ACID, isolation levels, locking, MVCC, and deadlocks.

  1. 18.Transactions and ACID
  2. 19.Isolation Levels
  3. 20.Locking and Deadlocks

Database Internals and Scaling

Storage, WAL, buffer pool, MVCC, query execution, replication, sharding, and SQL vs NoSQL.

  1. 21.Storage and Pages
  2. 22.WAL and Crash Recovery
  3. 23.MVCC and Vacuum
  4. 24.Query Execution and the Optimizer
  5. 25.LSM Trees vs B-Trees
  6. 26.Partitioning and Sharding
  7. 27.Replication and CAP
  8. 28.SQL vs NoSQL

Interview Practice

Classic SQL problems and the theory questions interviewers ask.

  1. 29.Classic SQL Interview Problems
  2. 30.SQL Interview Questions