SQL

Talk to databases

Almost every application relies on a relational database. Queries, joins, data modeling, transactions, window functions, then optimization: learn to query and design reliable, fast data.

Foundations

Beginner - the basics, step by step

• The relational model: tables, rows, columnsDiscover how a database organizes information into tables, and create your first table in SQLite.
• SELECT and WHERE: reading and filtering dataLearn to choose columns with SELECT, filter rows with WHERE and search for patterns with LIKE.
• Sorting and deduplicating: ORDER BY, LIMIT, DISTINCTMaster sorting results with ORDER BY, limiting the number of rows with LIMIT and removing duplicates with DISTINCT.
• Aggregating: COUNT, SUM, AVG, MIN/MAX and GROUP BYCompute totals, averages and per-group statistics using aggregate functions and GROUP BY.
• Modifying data: INSERT, UPDATE, DELETEAdd, modify and delete rows safely, avoiding the classic trap of the forgotten WHERE.

Progression

Intermediate - build real things

• INNER JOIN: connecting two tablesCross-reference data from two tables with INNER JOIN, readable aliases and a precise join condition.
• LEFT JOIN and NULL: finding what is missingKeep every row of a table with LEFT JOIN, spot absences with IS NULL and replace them with COALESCE.
• Subqueries: WHERE, FROM, IN and EXISTSNest one query inside another to compare against an aggregate, filter on a set or test for existence.
• Creating a schema: CREATE TABLE and constraintsDesign your own tables with suitable types and NOT NULL, UNIQUE and PRIMARY KEY constraints that protect your data.
• Foreign keys and referential integrityGuarantee consistency between your tables with REFERENCES and deliberately choose the ON DELETE behaviour.

Advanced

Proficient - advanced patterns and performance

• HAVING and advanced aggregationsFilter after aggregation with HAVING and pivot your data with CASE WHEN inside aggregate functions.
• CTEs (WITH): making complex queries readableStructure your queries into named steps with WITH and chain CTEs like a pipeline of transformations.
• Window functions: ROW_NUMBER, RANK, SUM OVERCompute ranks, running totals, and within-group comparisons without collapsing rows thanks to OVER and PARTITION BY.
• Transactions: BEGIN, COMMIT, ROLLBACK and ACIDMake your writes atomic with transactions and understand what the ACID guarantees mean in day-to-day practice.
• Data modeling: normalization and denormalizationStructure your schemas from 1NF to 3NF to eliminate anomalies, and know when to denormalize with full awareness.

Mastery

Expert - architecture, internals and edge cases

• EXPLAIN and query plans: read what the database actually doesDecode the plan that the SQLite planner really chooses for your queries with EXPLAIN QUERY PLAN.
• Advanced indexes: composite, covering, ignored indexMaster the leftmost-prefix rule, covering indexes, and the pitfalls that make an index invisible to the planner.
• Recursive queries (WITH RECURSIVE): hierarchies and graphsTraverse trees and graphs with WITH RECURSIVE, mastering depth and cycle detection.
• Concurrency: locks, isolation levels, phantom readsUnderstand SQLite's locking model, its transactions, and the isolation anomalies described by the SQL standard.
• Security: SQL injection, privileges, prepared statementsReproduce an SQL injection, neutralize it with prepared statements, and apply least privilege.