Skip to content
Tutorials Center
My learning

Topics

SQL Tutorial

Learn SQL by querying real databases in your browser. 125 lessons from SELECT to joins, window functions, database design, performance and PostgreSQL/MySQL differences, with quizzes and projects.

Start learning →

of lessons completed

Getting Started

  1. 1 Your First SQL Queries Learn what SQL is and write your first queries against a real SQLite database in your browser. Select columns, filter rows with WHERE and sort with ORDER BY.
  2. 2 Databases, Tables and Keys Learn how relational databases organize data into tables, rows and columns, how primary and foreign keys link tables, and how to read a schema in SQLite.
  3. 3 SQL Dialects and SQLite See how the SQL standard relates to SQLite, PostgreSQL, MySQL, SQL Server and Oracle, what really differs between them, and how to install a database client.
  4. 4 SQL Command Types: DDL, DML, DQL, DCL and TCL Learn the five families of SQL statements, DDL, DML, DQL, DCL and TCL, with a runnable example of each and a guide to which ones SQLite supports.
  5. 5 SQL Syntax and Style Learn the grammar of SQL statements, keywords versus identifiers, case sensitivity, single versus double quotes, comments, and a clean formatting style.
  6. 6 Creating, Selecting and Dropping Databases Learn how to create, select, list, rename and drop databases in PostgreSQL, MySQL and SQL Server, how schemas differ, and how SQLite uses ATTACH and DETACH.
  7. 7 SQL Careers and Where SQL Is Used See where SQL is used at work. Analyst, data engineer, DBA, BI developer and backend roles, the skills ladder for each, common certifications and industries, with live queries per role.

Querying Data

  1. 8 SELECT in Depth Master the SQL SELECT statement - choose and reorder columns, use SELECT * wisely, compute expressions and literals, and run SELECT without a FROM clause.
  2. 9 Filtering with WHERE Filter rows with the SQL WHERE clause. Use comparison operators on numbers, text and dates, quote values correctly, and avoid the classic date and type traps.
  3. 10 AND, OR and NOT Combine SQL conditions with AND, OR and NOT. Learn the truth tables, why AND binds tighter than OR, how parentheses fix it, and how NULL affects logic.
  4. 11 IN and BETWEEN Filter SQL rows by a list of values with IN and by a range with BETWEEN. Learn inclusive bounds, date and text ranges, and why NOT IN fails with NULL.
  5. 12 Pattern Matching with LIKE Match text patterns in SQL with LIKE and the % and _ wildcards. Learn case sensitivity across databases, ESCAPE, NOT LIKE, SQLite GLOB and regular expressions.
  6. 13 Working with NULL Understand NULL in SQL. Learn three-valued logic, IS NULL, how NULL behaves in comparisons, NOT IN, sorting and aggregates, and how IS DISTINCT FROM helps.
  7. 14 Sorting with ORDER BY Sort query results with SQL ORDER BY. Learn ASC and DESC, multiple sort columns, NULLS FIRST/LAST, sorting by expressions and aliases, custom orders and collations.
  8. 15 LIMIT, OFFSET and Pagination Return only the rows you need with SQL LIMIT and OFFSET. Build top-N queries, page through results, use fast keyset pagination and learn TOP and FETCH FIRST.
  9. 16 DISTINCT Remove duplicate rows with SQL SELECT DISTINCT. Learn DISTINCT on several columns, how NULLs are treated, COUNT(DISTINCT), DISTINCT vs GROUP BY and DISTINCT ON.
  10. 17 Column and Table Aliases Rename columns, expressions and tables with SQL aliases and AS. Learn quoting rules, table aliases in joins, and exactly where each database lets you use an alias.
  11. 18 SQL Operators A complete guide to SQL operators - arithmetic, comparison, logical, string concatenation with ||, bitwise operators - plus an operator precedence table.

Functions and Expressions

  1. 19 Calculated Columns Build new columns in SQL from existing ones. Compute totals, taxes and percentages, join text, avoid the integer division trap and handle NULL and floating-point results.
  2. 20 String Functions Clean, cut and format text in SQL with length, upper, lower, substr, trim, replace, instr, concat and printf, plus the names other databases use for each.
  3. 21 Numeric Functions Round prices, round up and down, take absolute values, divide without surprises and use powers, roots, logs and random numbers in SQL, with dialect notes.
  4. 22 Date and Time Functions Store, filter, format and calculate dates in SQL with SQLite's date(), time(), datetime(), strftime(), julianday() and modifiers, plus the PostgreSQL, MySQL and SQL Server versions.
  5. 23 CASE Expressions Add if-then-else logic to SQL with CASE WHEN. Label rows, sort in custom orders, count conditionally, avoid errors and use iif() in SQLite, MySQL and SQL Server.
  6. 24 COALESCE, IFNULL and NULLIF Replace missing values with defaults using COALESCE and IFNULL, turn placeholder values into NULL with NULLIF, and avoid division-by-zero errors in SQL.
  7. 25 Types and CAST Learn how SQLite stores values with storage classes and type affinity, check types with typeof(), convert them with CAST, and avoid implicit conversion bugs.
  8. 26 Working with JSON Store, validate, query and build JSON in SQL. Extract fields with json_extract, -> and ->>, expand arrays with json_each and create JSON results with json_group_array.
  9. 27 Regular Expressions in SQL Match, validate, extract and clean text in SQL with regular expressions. Learn REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_COUNT and PostgreSQL's ~ operators.

Aggregation

  1. 28 Aggregate Functions Summarize whole tables with COUNT, SUM, AVG, MIN and MAX, learn how each one treats NULL, and combine values into lists with group_concat and string_agg.
  2. 29 GROUP BY Learn how SQL GROUP BY splits rows into groups so COUNT, SUM and AVG work per group. Group by several columns and expressions, and avoid the classic mistakes.
  3. 30 HAVING Learn how SQL HAVING filters groups after GROUP BY, when to use WHERE instead, and the logical order in which every part of a SELECT statement runs.
  4. 31 Grouping in Practice Build real SQL reports with GROUP BY - revenue per month and quarter, top customers and products, percentages of a total, rates and averages of order totals.
  5. 32 Subtotals: ROLLUP, CUBE and GROUPING SETS Add subtotal and grand-total rows to SQL reports with ROLLUP, CUBE and GROUPING SETS, use GROUPING() to label them, and build the same reports in SQLite with UNION ALL.
  6. 33 Pivoting Rows into Columns Pivot SQL results into a cross-tab with conditional aggregation (CASE and FILTER), unpivot columns back into rows with UNION ALL, and compare PIVOT in SQL Server and Oracle.

Joins

  1. 34 Why Joins? Understand why relational data is split across tables and how SQL joins put it back together. Learn the join mindset, keys, and the join types you'll use.
  2. 35 INNER JOIN Master SQL INNER JOIN - write ON conditions, use USING for shared column names, join on several columns or ranges, and see why NATURAL JOIN is risky.
  3. 36 LEFT JOIN Learn SQL LEFT JOIN - keep every row of the left table, count zero matches correctly, find missing rows with anti-joins, and see why ON vs WHERE matters.
  4. 37 RIGHT and FULL OUTER JOIN Learn how SQL RIGHT JOIN and FULL OUTER JOIN keep unmatched rows, how to find orphans on both sides, compare two lists, and emulate FULL JOIN in MySQL.
  5. 38 Self Joins Learn SQL self joins by joining a table to itself. List employees with their managers, count direct reports, walk up the hierarchy and pair rows without duplicates.
  6. 39 NATURAL JOIN and USING Learn the SQL shorthand joins USING and NATURAL JOIN, how they merge columns, how they differ from ON and why NATURAL JOIN is risky.
  7. 40 CROSS JOIN Learn how SQL CROSS JOIN pairs every row with every other row, how to generate combinations, calendars and report grids, and how to avoid accidental cartesian products.
  8. 41 Joining Many Tables Learn to join three or more tables in SQL, read join chains, mix INNER and LEFT joins safely, and avoid row multiplication (fan-out) that inflates sums and counts.

Subqueries and Set Operations

  1. 42 UNION, INTERSECT and EXCEPT Combine query results with SQL set operations. Learn UNION vs UNION ALL, INTERSECT, EXCEPT, column matching rules, sorting, precedence and dialect differences.
  2. 43 Subqueries Learn SQL subqueries step by step. Use scalar subqueries, IN lists and derived tables, avoid the NOT IN NULL trap, and know when a subquery beats a join.
  3. 44 Correlated Subqueries Learn SQL correlated subqueries that run once per outer row. Compare rows with their group, find the latest row per group, avoid scoping bugs and keep them fast.
  4. 45 EXISTS Learn SQL EXISTS and NOT EXISTS for semi-joins and anti-joins, why NOT EXISTS beats NOT IN with NULLs, how to answer "for all" questions, and ANY/ALL in other databases.
  5. 46 ANY, SOME and ALL with Subqueries Learn SQL ANY, SOME and ALL: compare a value with every row of a subquery, map them to IN, MIN and MAX, and avoid the empty-set and NULL traps.
  6. 47 Common Table Expressions Learn SQL common table expressions (CTEs). Name subqueries with WITH, chain several steps, reuse a result twice and control materialization in SQLite.
  7. 48 Recursive CTEs Learn recursive CTEs in SQL with WITH RECURSIVE. Generate number and date series, walk an org chart, find manager chains and explore graphs safely.

Window Functions

  1. 49 Window Functions Learn SQL window functions with OVER, PARTITION BY and ORDER BY. Add totals, averages and shares to every row without collapsing rows like GROUP BY.
  2. 50 ROW_NUMBER, RANK and DENSE_RANK Learn ROW_NUMBER, RANK, DENSE_RANK and NTILE in SQL. Handle ties correctly, find the top N rows per group, remove duplicates and split rows into buckets.
  3. 51 Running Totals and Moving Averages Compute running totals, cumulative counts, moving averages and running maximums in SQL with SUM() OVER and ROWS frames, and avoid the classic tie trap.
  4. 52 LAG, LEAD and Comparing Rows Use LAG and LEAD to compare each row with the previous or next one, compute month-over-month change and gaps between events, and use FIRST_VALUE and LAST_VALUE.
  5. 53 Window Frames in Depth Master SQL window frames. Compare ROWS, RANGE and GROUPS, set frame boundaries, use EXCLUDE, share named windows with WINDOW and filter aggregates with FILTER.
  6. 54 Percentiles, Medians and Statistics Compute medians and percentiles in SQL, use PERCENT_RANK and CUME_DIST, calculate standard deviation and z-scores, and solve gaps-and-islands problems with windows.
  7. 55 Gaps and Islands Find consecutive runs and missing ranges in SQL with window functions. Learn the ROW_NUMBER difference trick, login streaks, gap detection and status changes.
  8. 56 Time Series and Calendar Tables Analyze time series in SQL. Build a calendar table, fill missing days, bucket by week and month, compare year over year and smooth data with moving averages.

Changing Data

  1. 57 INSERT Learn SQL INSERT INTO step by step. Add one or many rows, rely on column defaults, copy rows with INSERT ... SELECT and read generated ids with RETURNING.
  2. 58 UPDATE Learn SQL UPDATE with live examples. Change one or many columns, compute new values, update from other tables with UPDATE ... FROM, and avoid the missing-WHERE disaster.
  3. 59 DELETE Learn SQL DELETE with live examples. Remove rows with WHERE and subqueries, delete duplicates, see how foreign keys block or cascade, and compare DELETE with TRUNCATE.
  4. 60 Upserts with ON CONFLICT Learn SQL upserts. Insert a row or update it when it already exists with ON CONFLICT DO UPDATE and DO NOTHING, avoid REPLACE pitfalls and compare MySQL and MERGE.
  5. 61 MERGE: Insert, Update and Delete in One Statement Learn the SQL MERGE statement: sync a target table from a source with matched and not matched branches, deletes, and fallbacks for SQLite and MySQL.
  6. 62 Transactions Learn SQL transactions with live examples. Group statements with BEGIN, COMMIT and ROLLBACK, undo part of the work with savepoints, and understand what ACID means.
  7. 63 Isolation Levels and Locking Understand SQL isolation levels. Dirty, non-repeatable and phantom reads, lost updates, locking vs MVCC, SELECT FOR UPDATE, deadlocks, and how SQLite handles concurrency.
  8. 64 Importing and Exporting Data Learn how to import and export SQL data. CSV with SQLite, PostgreSQL COPY, MySQL LOAD DATA and SQL Server BULK INSERT, plus INSERT scripts, JSON and fast bulk loads.
  9. 65 Data Cleaning and Deduplication Clean messy data with SQL. Trim and normalize text, turn placeholders into NULL, find duplicate rows with GROUP BY and ROW_NUMBER, then delete them safely.

Designing Databases

  1. 66 CREATE TABLE Learn SQL CREATE TABLE with live examples. Define columns, types, primary keys and defaults, use IF NOT EXISTS and STRICT tables, and copy query results with CREATE TABLE AS.
  2. 67 Temporary Tables, CREATE TABLE AS and SELECT INTO Copy tables and results with CREATE TABLE AS and SELECT INTO, build temporary tables, and compare SQLite, PostgreSQL, MySQL, SQL Server and Oracle.
  3. 68 Primary, Candidate, Composite and Foreign Keys Learn every kind of SQL key โ€“ super, candidate, primary, alternate, composite, surrogate and natural keys โ€“ and how to choose the right key for a table.
  4. 69 Data Types Choose the right SQL data type for every column. Compare SQLite storage classes with PostgreSQL, MySQL and SQL Server types, and store money and dates safely.
  5. 70 Auto-Increment and Identity Columns Let the database number your rows. Learn SQLite's ROWID and AUTOINCREMENT, IDENTITY, SERIAL and AUTO_INCREMENT in other databases, sequences and UUID keys.
  6. 71 Sequences and Generating Numbers Learn SQL sequences: CREATE SEQUENCE options, NEXTVAL and NEXT VALUE FOR across databases, why gaps happen and how to generate number series.
  7. 72 Constraints Make the database reject bad data. Learn NOT NULL, UNIQUE, CHECK, DEFAULT and PRIMARY KEY constraints, named constraints and generated columns, with live examples.
  8. 73 Foreign Keys Keep references between tables valid with SQL foreign keys. Learn REFERENCES, PRAGMA foreign_keys in SQLite, ON DELETE and ON UPDATE actions, and deferred checks.
  9. 74 ALTER TABLE and DROP TABLE Change tables that already hold data. Add, rename and drop columns with ALTER TABLE, rebuild tables safely in SQLite, drop tables and manage schema migrations.
  10. 75 Indexes Learn how SQL indexes make queries fast. Create single, unique and composite indexes, read EXPLAIN QUERY PLAN output and know when an index helps or hurts.
  11. 76 Views Save queries as virtual tables with SQL views. Learn CREATE VIEW, how views stay up to date, updatable views, INSTEAD OF triggers and materialized views.
  12. 77 Entity-Relationship Modeling Learn entity-relationship modeling. Read and draw ER diagrams, understand cardinality and crow's-foot notation, and turn a diagram into real SQL tables.
  13. 78 Normalization Learn database normalization in plain language. Spot update, insert and delete anomalies, apply 1NF, 2NF, 3NF and BCNF, and know when to denormalize.
  14. 79 One-to-Many and Many-to-Many Model real systems in SQL. Build one-to-many, one-to-one and many-to-many relationships with foreign keys and junction tables, then query them both ways.
  15. 80 Triggers Learn SQL triggers with runnable examples. Write BEFORE, AFTER and INSTEAD OF triggers, build audit logs, validate data and keep totals in sync automatically.
  16. 81 Hierarchical Data: Adjacency, Nested Sets, Closure Tables Model trees such as categories and org charts in SQL. Compare the adjacency list, nested sets and closure table, and query descendants, ancestors and depth.
  17. 82 Versioning, Audit Tables and Slowly Changing Dimensions Keep the history of changing data in SQL. Learn Type 1, 2 and 3 slowly changing dimensions, valid-from and valid-to rows, as-of queries and audit triggers.
  18. 83 Data Warehousing: Star Schema, OLTP vs OLAP Learn how data warehouses differ from transactional databases. Build a star schema with fact and dimension tables, run analytic queries and add aggregate tables.
  19. 84 Multi-Tenant Schema Design Design a SQL database that serves many customers safely. Compare shared tables, separate schemas and separate databases, and enforce tenant isolation with keys.
  20. 85 Schema Migrations and Zero-Downtime Changes Learn how to change a live database safely. Write versioned SQL migrations, track them in a table, and rename or split columns with the expand/contract pattern.
  21. 86 Designing a Database for Real-Time Reporting Design a SQL database for fast dashboards: summary tables, incremental aggregates, materialized views, partitioning, read replicas, CDC and covering indexes.

Performance

  1. 87 Reading Query Plans Learn to read query plans with EXPLAIN QUERY PLAN. Tell SCAN from SEARCH, spot covering indexes and temp B-trees, and read EXPLAIN ANALYZE in PostgreSQL.
  2. 88 Writing Fast Queries Write fast SQL queries. Keep conditions sargable, select only what you need, choose between EXISTS, IN and JOIN, fix N+1 queries and measure every change.
  3. 89 Partial, Expression and Covering Indexes Design indexes that fit your queries. Use partial and expression indexes, build covering indexes, choose column order, weigh write costs and run ANALYZE.
  4. 90 Tuning the Database Server Tune a database server: buffer pool and shared_buffers, work_mem, connections and pooling, autovacuum and statistics, checkpoints, WAL and monitoring.
  5. 91 Replication, Read Replicas and Sharding Learn how databases scale beyond one server. Understand replication, read replicas, replication lag, sharding keys and the consistency tradeoffs of each.
  6. 92 Table Partitioning and Tablespaces Split huge tables into partitions by range, list or hash. Learn partition pruning, fast data retirement, PostgreSQL and MySQL syntax, and where tablespaces fit in.
  7. 93 Connection Pooling and Plan Caching Learn why connections are expensive, how to size a connection pool, and how prepared statements let the database reuse query plans. Covers PgBouncer and pitfalls.

Database Systems

  1. 94 PostgreSQL Essentials Move from SQLite to PostgreSQL. Learn psql basics, strict types, IDENTITY columns, RETURNING, arrays, JSONB, ILIKE, FILTER and DISTINCT ON, with SQLite equivalents.
  2. 95 MySQL and MariaDB Essentials Learn what makes MySQL and MariaDB different, from backticks, AUTO_INCREMENT and ON DUPLICATE KEY UPDATE to InnoDB, collations and GROUP BY modes.
  3. 96 SQL Server and Oracle Essentials Learn the key differences in Microsoft SQL Server and Oracle, from TOP, IDENTITY, square brackets and MERGE to FETCH FIRST, sequences, DUAL and empty strings.
  4. 97 Stored Procedures and Functions Learn stored procedures and user-defined functions in PostgreSQL, MySQL and SQL Server, with parameters, variables, loops, cursors and errors, plus SQLite alternatives.
  5. 98 Users, Roles and Permissions (DCL) Learn how databases control access with users, roles, GRANT and REVOKE, how to apply least privilege, and how row-level security works in PostgreSQL and SQL Server.
  6. 99 SQL Injection and Secure Queries See how SQL injection works with live examples, then learn to prevent it with parameterized queries, allowlists, safe dynamic SQL and least privilege in every language.
  7. 100 Dynamic SQL Build SQL at run time without opening injection holes. Learn PREPARE/EXECUTE, sp_executesql, EXECUTE format(), identifier quoting, search builders and the limits of dynamic SQL.
  8. 101 Encrypting Data in the Database Learn how databases protect data in transit with TLS, at rest with TDE and per column with pgcrypto or Always Encrypted, plus password hashing and keys.
  9. 102 Backups, Migrations and Maintenance Learn how teams back up and restore databases, version schemas with migration files, and keep databases healthy with VACUUM, ANALYZE and integrity checks.
  10. 103 SQL vs NoSQL Compare relational SQL databases with document, key-value, wide-column and graph stores. Learn the real trade-offs and how SQL's JSON features bridge both worlds.
  11. 104 Connecting to Databases from Code: Drivers, ODBC and ORMs Learn how apps talk to databases through drivers (JDBC, ODBC, DB-API, PDO), connection strings, pools, parameterized queries, ORMs and the N+1 problem.
  12. 105 Where to Host a SQL Database Compare SQL hosting options: on-premises, VPS, shared hosting, managed cloud databases and serverless. Trade-offs in cost, backups, scaling and compliance.
  13. 106 Geospatial Data in SQL Store locations in SQL, compute distances with the haversine formula, find the nearest places with a bounding box, and learn how PostGIS and SpatiaLite do it.

Projects

  1. 107 Project: Sales Analysis Analyze a year of online-store sales with SQL. Define revenue, chart monthly trends, rank products and customers, catch data problems and build a cohort table.
  2. 108 Project: Design a Library Database Design a library database from scratch in SQL. Model books, copies, members and loans, enforce rules with constraints and triggers, seed data and answer real questions.
  3. 109 Project: HR Analytics Analyze a company's people data with SQL. Report headcount and payroll, build salary bands, compare pay within roles, draw the org chart with recursive CTEs and study tenure.
  4. 110 Project: School Report Cards Build school reports with SQL. Turn grades into credit-weighted GPAs, rank students with window functions, pivot grades into report cards and flag at-risk students.
  5. 111 Sessionization, Funnels and Retention Analyze event data with SQL. Split clicks into sessions with LAG and running sums, build conversion funnels, and compute day-1 and day-7 retention cohorts.
  6. 112 Project: E-commerce Funnel Analytics Build an e-commerce analytics report in SQL. Measure a customer funnel, split new from returning revenue, build a retention cohort table and test revenue concentration.
  7. 113 Project: Inventory and Orders Database Design an inventory and orders database from scratch. Model products, stock and orders, protect the data with constraints, and keep it consistent with transactions.
  8. 114 Project: Banking Database Build and audit a banking database in SQL. Guard a ledger with constraints and triggers, move money atomically, reconcile balances, print statements and keep an audit trail.
  9. 115 Project: Bookstore Customer Analytics Analyze an online bookshop with SQL. Segment customers with RFM, measure repeat and lapsed buyers, find books bought together, compare marketing ROI and flag low stock.
  10. 116 Project: Social Media Engagement Analytics Analyze a social network with SQL. Avoid the join fan-out trap, compute engagement rates, rank creators, track hashtag trends and chart follower growth and active users.
  11. 117 Project: Healthcare Data and KPIs Build hospital KPIs in SQL. Compute billing and length of stay, count diagnoses, protect small groups, check data quality, calculate 30-day readmission and publish a dashboard view.
  12. 118 Project: Music Streaming Analytics Analyze a music streaming service with SQL. Build weekly charts with rank movement, measure skip rate, group plays into listening sessions, summarize playlists and chart retention.
  13. 119 Project: Ride Booking Analysis Analyze a ride-booking service with SQL. Break down ride outcomes, find peak hours, compare peak and off-peak cancellations, define surge windows, rank driver revenue and study ratings.

Practice and Review

  1. 120 SQL Cheat Sheet A one-page SQL cheat sheet covering SELECT, filters, joins, aggregates, subqueries, CTEs, window functions, INSERT/UPDATE/DELETE, DDL and dialect differences.
  2. 121 SQL Queries: 50 Classic Problems 50 classic SQL query problems with runnable answers, from the second-highest salary and duplicates to top-N per group, running totals, gaps and pivots.
  3. 122 SQL Practice Exercises 30 graded SQL exercises with solutions on filtering, functions, GROUP BY, joins, subqueries, CTEs, window functions and changing data. Run them in your browser.
  4. 123 SQL Interview Questions 50 common SQL interview questions with clear answers, from keys, joins and GROUP BY to window functions, indexes, transactions and isolation levels.
  5. 124 SQL Puzzles and Interview Patterns Master the SQL patterns interviews love: top-N per group, running totals, consecutive-day streaks, missing-ID gaps and duplicate removal.
  6. 125 Row Selection Patterns: Odd/Even, Every Nth, Last N and Random Rows Select odd or even rows, every Nth row, the first half, the last N rows, the middle row and a random row in SQL, using ROW_NUMBER, NTILE and LIMIT.