Welcome to Database Interview Prep

This course covers the relational database concepts interviewers actually probe on: tables and schemas, keys, CRUD operations, joins, subqueries and CTEs, sequences, ER modeling, indexing strategies, normalization through BCNF, transactions and isolation levels, and advanced features like views, stored procedures, and performance tuning.

What You'll Learn

• Database Fundamentals - RDBMS concepts, data types, constraints, schema design basics
• Keys - Primary, foreign, unique, candidate, composite, super, surrogate vs natural keys
• CRUD Operations - INSERT, SELECT, UPDATE, DELETE with RETURNING, UPSERT, and CASE
• Filtering, Sorting and Aggregation - WHERE, LIKE/ILIKE, MIN/MAX/AVG/SUM/COUNT, GROUP BY, HAVING, ORDER BY
• Joins - INNER, LEFT, RIGHT, FULL OUTER, CROSS, self joins, and join performance
• Subqueries and Advanced Queries - Correlated vs non-correlated, UNION/INTERSECT/EXCEPT, CTEs, window functions
• Sequences and Auto-Increment - SERIAL, IDENTITY, UUID primary keys
• Relationships and ER Diagrams - One-to-one, one-to-many, many-to-many, ER modeling, cardinality and ordinality
• Indexing - B-Tree, GIN, GiST, Hash, BRIN, covering indexes, EXPLAIN ANALYZE
• Normalization - 1NF through BCNF and the trade-offs with denormalization
• Transactions and Concurrency - ACID, isolation levels (Read Uncommitted -> Serializable), dirty/non-repeatable/phantom reads
• Advanced Topics - Views and materialized views, stored procedures and functions, performance optimization

How to Use This Course

Read the chapters in order - joins build on keys, subqueries build on joins, indexing builds on everything before it. Examples use PostgreSQL by default with MySQL notes where syntax differs. After each lesson, re-explain the concept out loud and try the SQL on a real test database.

Prerequisites

• Basic comfort with the terminal and a text editor
• Any working SQL client (psql, DBeaver, TablePlus, pgAdmin) connected to PostgreSQL 14+
• Optional but useful: prior exposure to a programming language so the concepts map to data your apps already touch

Let's start with what a database actually is.