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