SQL Concepts for Interviews and Everyday Queries

This course contains the use of artificial intelligence.  SQL Concepts for Interviews and Everyday Queries is a practical course on writing SQL queries and reasoning about their results. You will learn how to filter, join, group and rank data, handle NULL values correctly, break a complex query into readable steps, change data inside transactions, read a query plan, and explain your solution to an SQL interview question.

Every lesson starts from a question about example data and shows how the rows change before introducing the syntax that produces them. The examples use a fictional shop database of customers, orders, order items and products, plus a small employee hierarchy for self joins and recursive queries. PostgreSQL 18 is the reference dialect, PostgreSQL-specific syntax is named where it matters, and every result shown comes from running the query in PostgreSQL. Each lesson also resolves a common misconception, the kind of trap that SQL interview questions are built on.

Relational foundations

• Tables, rows, columns and what a SELECT query returns without changing the stored table

• Primary keys, foreign keys and one-to-many table relationships

• Database normalization and the update anomalies it prevents

• SQL data types, CAST and why five divided by two can return two

• The logical order of a SELECT query: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and LIMIT

Selecting and transforming data

• SELECT expressions, calculated columns and aliases with AS

• WHERE filters with AND, OR, IN, BETWEEN and LIKE, and why parentheses matter

• NULL, three-valued logic and IS NULL

• DISTINCT, ORDER BY and LIMIT with a unique tie-breaker for repeatable results

• CASE, COALESCE and NULLIF, text cleaning with TRIM and LOWER, half-open date ranges, and parameterized queries with PREPARE and EXECUTE

Summarizing data

• Aggregate functions COUNT, SUM, AVG, MIN and MAX, including NULLs and empty input

• GROUP BY and result granularity: what one output row represents

• WHERE vs HAVING, conditional aggregation and finding duplicate records

• Daily totals with date_trunc and a calendar from generate_series to show missing dates

Joining and combining data

• INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN, and ON vs WHERE filters

• CROSS JOIN combinations and self joins for employee and manager lookups

• Join multiplication (fan-out): why totals inflate after a one-to-many join and how to fix them at the right grain

• UNION, UNION ALL, INTERSECT and EXCEPT

Subqueries and multi-step queries

• Scalar subqueries and derived tables

• EXISTS, NOT EXISTS and the NOT IN trap with NULL values

• Common table expressions (CTEs) with WITH

• Recursive CTEs for reporting hierarchies, with depth and cycle guards

Window functions and analytical patterns

• Window functions with OVER and PARTITION BY, compared with GROUP BY

• ROW_NUMBER, RANK and DENSE_RANK, ties and the second highest value

• Top results and the latest record per group

• Running totals and moving averages with ROWS window frames

• LAG and LEAD for change between rows, and gaps and islands for consecutive-day streaks

Defining and changing data

• CREATE TABLE with PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE and CHECK constraints

• INSERT, UPDATE, DELETE and RETURNING

• Transactions with BEGIN, COMMIT and ROLLBACK

• Read Committed isolation, row locks and concurrent updates in two sessions

Reuse, performance and interview reasoning

• Views vs materialized views and REFRESH

• Indexes, EXPLAIN and EXPLAIN ANALYZE: estimated rows, actual rows and scan choices

• Solving an SQL interview question: state the output grain, handle ties and NULLs, and test edge cases

Practical tools you can use at work

Practice every topic with downloadable resources:

• SQL Reference and Practice Companion

• SQL Practice Questions and Answers

• SQL Practice Files, with a PostgreSQL script for each topic