SQL lessons: a free course from the basics to analytics

A database is an organized collection of data managed by a DBMS. Learn database types, core DBMS functions, and why SQL is the universal data language.

The main database types: relational, key-value, document, wide-column, graph, time-series and columnar, with their differences and use cases.

The building blocks of relational databases: tables, columns, rows, primary and foreign keys, and ACID transaction properties, with examples.

SQL data types define what values a column can store. Learn numeric, string, date/time, boolean, and other types with practical examples and selection tips.

A short tour of SQL data types that store structure: JSON, XML, arrays, ENUM and SET, BLOB, UUID, spatial types and VECTOR for AI embeddings.

What NULL means in a relational database, how it differs from 0, an empty string and false, which columns allow it, and why it makes logic three-valued.

SQL is the standard language for relational databases. Learn how SQL is organized, what its subsets are, and how a basic query is built.

Learn core SQL SELECT basics: choosing all or specific columns, controlling output order, and using DISTINCT to remove duplicates.

The SQL WHERE clause filters rows by a condition. Learn comparison operators, BETWEEN, IN, LIKE patterns, and how to handle NULL correctly.

Learn logical operators AND, OR, and NOT in SQL WHERE, operator precedence, parentheses, and practical NOT IN and NOT LIKE examples.

Why WHERE column = NULL returns nothing, how IS NULL and IS NOT NULL work, and how NULL hides rows in NOT, <> and NOT IN conditions.

A SQL column alias gives a column a temporary name in the result. Learn the AS keyword, aliases with spaces, aliases for calculations, and where they work.

SQL ORDER BY sorts query results. Learn ascending and descending order, sorting by several columns, by expressions and aliases, and where NULL values end up.

SQL LIMIT restricts how many rows a query returns, and OFFSET skips rows. Learn top-N queries, pagination, why ORDER BY is required, and TOP / FETCH FIRST.

Combine WHERE, ORDER BY and LIMIT in one SQL query. Learn the required clause order, the logical processing order, and filtered top-N and pagination queries.

Learn how built-in SQL functions work in SELECT and WHERE clauses, and apply them to real Sakila-based data analysis scenarios.

Learn the core SQL string functions with Sakila examples: clean, combine, and extract text data in practical query workflows.

Learn core SQL mathematical functions with Sakila examples: rounding, modulo, powers, roots, and practical calculation scenarios.

SQL date and time functions get the current date, extract parts, add intervals, calculate differences and format dates. Examples with NOW, DATE_ADD, DATEDIFF.

SQL CASE WHEN adds if-then-else logic to a query. Learn simple and searched CASE, labels and categories in SELECT, custom sort order, and conditions in WHERE.

Read values from JSON columns by path, filter rows by them, count array items and build JSON with JSON_VALUE, ->>, JSON_OBJECT and JSON_ARRAYAGG.

Learn core SQL aggregate functions with Sakila examples: COUNT, SUM, AVG, MIN, MAX, plus differences between COUNT(*), COUNT(column), and COUNT(DISTINCT ...).

Learn GROUP BY: syntax, rules, single and multi-column grouping, practical Sakila examples.

Learn HAVING clause: syntax, WHERE vs HAVING differences, filtering groups after aggregation, Sakila examples.

Conditional aggregation calculates several metrics in one SQL query with SUM, COUNT and AVG over CASE WHEN. Learn the patterns, common mistakes, and pivots.

SQL ROLLUP, CUBE and GROUPING SETS add subtotals and grand totals to GROUP BY results. Learn how they differ, WITH ROLLUP in MySQL, and the GROUPING() function.

A SQL JOIN combines rows from two or more tables by a related column. Learn how JOIN works, the ON condition and table aliases, on Sakila data.

SQL INNER JOIN returns only rows that have a match in both tables. Learn the syntax, table aliases, joining three tables, and what happens to unmatched rows.

SQL LEFT JOIN returns all rows of the left table and matching rows of the right one, with NULL where none match. Learn syntax, anti-joins and pitfalls.

SQL RIGHT JOIN returns all rows from the right table and the matching rows from the left one. Learn the syntax, how it mirrors LEFT JOIN, and when to use it.

SQL FULL OUTER JOIN returns all rows from both tables, with NULL where there is no match. Learn the syntax and how to emulate it in MySQL and MariaDB.

SQL CROSS JOIN returns every combination of rows from two tables. Learn the syntax, the result size, and how to build report grids and fill gaps.

A SQL self join joins a table to itself to compare rows or follow a hierarchy. Learn why aliases are required and how to avoid duplicate pairs.

Practical SQL JOIN examples: joining several tables, aggregating joined data, finding missing rows, and avoiding inflated totals and lost outer rows.

How databases execute joins: nested loop, hash join and merge join. Learn how each algorithm works, when the planner picks it, and how to see it in EXPLAIN.

Dataset operations let you combine, intersect, and subtract query results. Learn UNION, INTERSECT, and EXCEPT with Sakila examples.

A subquery is a SELECT inside another query. Learn subquery types, execution flow, and Inline View with practical Sakila examples.

Subqueries in WHERE let you filter data dynamically. Learn IN, NOT IN, EXISTS, NOT EXISTS, ANY, and ALL with practical Sakila examples.

A SQL correlated subquery uses values from the outer query for each row. Learn how it works, compare rows with their group, use EXISTS, mind performance.

A SQL CTE is a named temporary result set defined with WITH. Learn CTE syntax, CTE vs subquery, chaining multiple CTEs, and practical analysis examples.

A SQL recursive CTE queries hierarchies with WITH RECURSIVE. Learn the anchor and recursive members, org chart paths, depth limits and cycle protection.

Practical SQL recursive CTE examples: generate a date series to fill gaps, total a category subtree, explode a bill of materials and find routes in a graph.

SQL window functions calculate across related rows without collapsing them. Learn OVER, PARTITION BY and ORDER BY, window vs GROUP BY, and percent of total.

The difference between ROW_NUMBER, RANK, DENSE_RANK and NTILE in SQL: how each handles ties, when to use which, plus PERCENT_RANK and CUME_DIST, on real data.

SQL window frames set which rows a window function uses. Learn ROWS vs RANGE, BETWEEN, UNBOUNDED, the default frame, moving averages and the WINDOW clause.

Learn LAG, LEAD, FIRST_VALUE, and LAST_VALUE: syntax, common use cases, row-to-row comparison without self joins, and the important LAST_VALUE frame nuance.

SQL INSERT INTO adds new rows to a table. Learn the syntax, multi-row inserts, INSERT INTO ... SELECT, NULL and DEFAULT values, and common errors.

SQL UPDATE changes existing rows. Learn SET and WHERE, updating several columns, calculating from current values, updates by another table, and safety.

SQL DELETE removes rows from a table. Learn DELETE ... WHERE, deleting by another table, foreign key errors, checking first, and DELETE vs TRUNCATE.

A SQL transaction groups statements into one unit: all succeed or none do. Learn COMMIT, ROLLBACK, SAVEPOINT, autocommit and isolation levels.

SQL CREATE TABLE creates a table. Learn data types, PRIMARY KEY, AUTO_INCREMENT, NOT NULL, UNIQUE, DEFAULT, CHECK and FOREIGN KEY constraints.

SQL ALTER TABLE changes an existing table. Learn to add, modify, rename and drop columns, and to add or drop constraints in MySQL and PostgreSQL.

SQL TRUNCATE empties a table and DROP TABLE removes it. Learn how both differ from DELETE in speed, rollback, auto-increment and foreign keys.

SQL temporary tables store intermediate results for one session. Learn CREATE TEMPORARY TABLE, AS SELECT, temp tables vs CTEs, and MySQL pitfalls.

Learn SQL views: CREATE VIEW syntax, differences from regular and temporary tables, updatability, and practical Sakila examples.

A materialized view stores the result of a query on disk and is refreshed on demand. Learn CREATE and REFRESH MATERIALIZED VIEW and the MySQL workaround.

Database normalization splits data into related tables so each fact is stored once. Learn 1NF, 2NF, 3NF and BCNF, update anomalies and denormalization.

SQL best practices for readable code: keyword case, line breaks and indentation, snake_case names, clear aliases, comments, and a cleanup of a messy query.

Efficient SQL queries reduce server load and speed up reports. Learn practical filtering, JOIN, SARGable, and LIMIT techniques.

Learn what SQL indexes are, how they affect SELECT performance, and which basic rules help avoid common query speed mistakes.

Read EXPLAIN and EXPLAIN ANALYZE step by step: access types, join order, cost and rows, estimates vs actual rows, and the red flags of a slow SQL query.

Understand how B-tree indexes are structured in SQL, why they accelerate search to logarithmic complexity, and how to use them in practical queries.

A full-text index finds rows by words in text columns, where LIKE '%word%' scans the whole table. Learn MySQL MATCH AGAINST and PostgreSQL full-text search.

Learn practical string processing in SQL: clean text values, extract email domains, build labels, and validate data quality.

Learn practical date and time functions in SQL: build daily and monthly metrics, calculate intervals, detect seasonality, and prepare analytical reports.

SQL task solution: find the fastest flight with one connection using a self-join of the flights table and interval arithmetic in PostgreSQL.

SQL task solution: average seat occupancy per airport and fare class with LEFT JOIN, COUNT FILTER and two levels of aggregation in PostgreSQL.

SQL task solution: build an aircraft seat map by splitting seat numbers with rtrim and right and collecting the letters into a JSON array with json_agg.

Find the second or Nth highest salary in SQL: MAX with a subquery, LIMIT OFFSET, DENSE_RANK, a correlated subquery, ties, NULL results and groups.

How to find duplicate rows in SQL with GROUP BY and HAVING, list every copy with a window function, delete duplicates while keeping one row, and prevent them.

Get the top N rows in each group in SQL: ROW_NUMBER with PARTITION BY, ties with RANK and DENSE_RANK, a correlated subquery, and LATERAL joins, on Sakila data.

Calculate a running total in SQL with SUM() OVER (ORDER BY): cumulative revenue, totals that reset each year, the ROWS vs RANGE trap and an old-style solution.

Solve gaps and islands in SQL: group consecutive dates with the ROW_NUMBER trick, find missing ranges with LEAD, and get each customer's longest streak.

Pivot rows into columns in SQL: conditional aggregation with SUM(CASE ...), a pivot by month, dynamic pivot in MySQL, the PIVOT operator and unpivot.