Every move a data hero needs.

Hero Handbook

Every query type a data hero needs - what it is, when to use it, and which villain it defeats.

These are categories by business purpose, not separate SQL languages - one query can easily belong to several at once.

Hero Pass download🔒 HERO PASS

📄 SQL Field Guide (PDF, 8 pages)

A printable companion to the Handbook, from first SELECT to interview-ready.

  1. 1. Recruit
  2. 2. Sidekick
  3. 3. Guardian
  4. 4. Legend

What's inside

  • ✓ Command families
  • ✓ Joins and CTEs
  • ✓ A window pattern picker
  • ✓ Performance triage
  • ✓ Interview decoder
Jump to section

Hero Ranks

The path every data hero climbs, from first query to legend.

  1. Rank 1: Recruit

    SQL foundations

    SELECTWHEREJOINGROUP BYCASECTEswindow functions
  2. Rank 2: Sidekick

    Analytics & BI

    metricsKPIsexploratory analysistime-series
  3. Rank 3: Guardian

    Data quality & governance

    profilingvalidationQAreconciliationcritical data element controls
  4. Rank 4: Engineer

    Data engineering

    transformationsincremental loadsMERGEstagingmodeling
  5. Rank 5: Legend

    Advanced

    performance tuningauditingmonitoringML feature prep

Query Types

Click a card to expand it.

Sidekick · Analytics & BI

Guardian · Data quality & governance

Engineer · Data engineering

Legend · Advanced

Command Families

Query Quest's missions focus on SELECT - reading data without changing it, which is also the safest place to learn. But working data engineers write DDL, DML, and transaction control daily; this section is a quick reference for when you get there.

Data Definition Language (DDL)

CREATEALTERDROP

Defines and changes the shape of the database itself - tables, columns, and constraints - rather than the rows inside them.

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
ALTER TABLE customers ADD COLUMN email TEXT;
DROP TABLE old_customers;

Data Manipulation Language (DML)

INSERTUPDATEDELETEMERGE

Adds, changes, or removes the rows inside tables that already exist.

INSERT INTO customers (name) VALUES ('Ava Stone');
UPDATE customers SET email = 'ava@example.com' WHERE id = 1;
DELETE FROM customers WHERE id = 99;

Transaction Control

BEGINCOMMITROLLBACK

Groups a set of changes so they all succeed together or all get undone together - no half-finished updates.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Dialect Atlas

Query Quest runs on PostgreSQL. Here's how its SQL compares with the other databases you'll meet at work.

One grammar, many accents

Standard SQL is defined by ISO/IEC 9075. ANSI first standardized it in 1986 (SQL-86) and ISO followed in 1987, which is why people still call it "ANSI SQL". The current edition is SQL:2023. Every major database implements the core of the standard and adds its own extensions on top. None of them implements the full standard - so each one speaks SQL with its own accent.

Why Query Quest uses PostgreSQL

PostgreSQL is one of the most widely used databases in the world, and it stays close to the standard. JOINs, GROUP BY, CTEs and window functions work almost the same everywhere, so what you learn here transfers to whatever database your job uses.

Same task, five dialects

TaskPostgreSQLMySQLSQL ServerSnowflakeDatabricks SQL
First 10 rowsLIMIT 10LIMIT 10TOP 10 (or OFFSET/FETCH)1LIMIT 10LIMIT 10
Join text|| or CONCATCONCAT (|| is OR by default)2+ or CONCAT (|| from 2025)3|| or CONCAT|| or concat
Today's dateCURRENT_DATECURRENT_DATE / CURDATE()CAST(GETDATE() AS date) (CURRENT_DATE from 2025)3CURRENT_DATEcurrent_date()
Days betweenend - start4DATEDIFF(end, start)DATEDIFF(day, start, end)DATEDIFF(day, start, end)DATEDIFF(end, start)
7 / 2 returns33.500033.50000053.5
Case-insensitive matchILIKELIKE (default collation)6LIKE (default collation)6ILIKEILIKE
Quote a name with spaces"Order Date"`Order Date`[Order Date]"Order Date"`Order Date`
Filter on a window resultCTECTECTEQUALIFYQUALIFY
UpsertINSERT … ON CONFLICT (or MERGE, v15+)INSERT … ON DUPLICATE KEY UPDATEMERGEMERGEMERGE INTO7
Auto-number IDGENERATED … AS IDENTITYAUTO_INCREMENTIDENTITY(1,1)AUTOINCREMENT / IDENTITYGENERATED ALWAYS AS IDENTITY7
  1. 1. SQL Server's OFFSET … FETCH NEXT 10 ROWS ONLY only works after an ORDER BY.
  2. 2. In MySQL, || means OR unless the PIPES_AS_CONCAT SQL mode is on, and that OR meaning is deprecated.
  3. 3. SQL Server 2025 and Azure SQL added the || operator and CURRENT_DATE. SQL Server 2022 and earlier need + / CONCAT and CAST(GETDATE() AS date).
  4. 4. In PostgreSQL, date - date returns a whole number of days. timestamp - timestamp returns an interval instead.
  5. 5. Snowflake keeps 6 decimal places when it divides whole numbers, so 7 / 2 is 3.500000.
  6. 6. MySQL's and SQL Server's default collations ignore case, so plain LIKE matches either case. A case-sensitive collation changes that.
  7. 7. In Databricks, MERGE INTO and identity columns are Delta table features.

Dialect profiles

PostgreSQL

Where you'll meet it: Startups, web backends, and any team that wants a free, standards-friendly database.

  • • ILIKE and rich text functions
  • • JSONB columns
  • • INSERT … ON CONFLICT upserts

Gotcha: Your home base. Watch integer division: 7 / 2 is 3, so CAST one side to numeric.

MySQL

Where you'll meet it: Web apps and content sites, often behind PHP, WordPress or a classic LAMP stack.

  • • AUTO_INCREMENT ids
  • • INSERT … ON DUPLICATE KEY UPDATE
  • • Backtick-quoted names

Gotcha: || is OR, not text joining - 'a' || 'b' returns 0. Use CONCAT('a', 'b').

SQL Server

Where you'll meet it: Microsoft and enterprise shops, often next to .NET, Excel and Power BI.

  • • TOP n instead of LIMIT
  • • [Bracketed] names
  • • MERGE for upserts

Gotcha: There's no LIMIT. Write SELECT TOP 10 … or ORDER BY … OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY.

Snowflake

Where you'll meet it: Cloud data warehouses for analytics and BI teams.

  • • QUALIFY to filter window results
  • • VARIANT for semi-structured data
  • • Time Travel to query a table as it was

Gotcha: Unquoted names are stored in UPPERCASE. Create a column as "OrderDate" in quotes and you must quote it exactly that way every time.

Databricks SQL

Where you'll meet it: Cloud lakehouses, where data engineering and machine learning share the same tables.

  • • QUALIFY
  • • MERGE INTO on Delta tables
  • • Rich array and map functions

Gotcha: Quote names with backticks, not double quotes - "Order Date" is a string, not a column.

Write portable SQL

  1. Use CASE WHEN instead of vendor shortcuts like IF() or IIF().
  2. Use COALESCE instead of IFNULL, ISNULL or NVL.
  3. Use CAST(x AS type) instead of the :: shorthand or CONVERT().
  4. Write standard JOIN … ON instead of commas in FROM, and split big queries into CTEs (WITH).
  5. When a query must run in several databases, skip vendor-only functions - or keep them in one clearly marked place.

Popularity check (November 2026): DB-Engines' top four are Oracle, MySQL, SQL Server and PostgreSQL, with Snowflake (6th) and Databricks (7th) rising. PostgreSQL is the most used, most admired and most desired database in the 2026 Stack Overflow Developer Survey - the same spots it held in 2025.

DB-Engines Ranking ↗Stack Overflow Developer Survey 2026: Databases ↗Stack Overflow Developer Survey 2025: Technology ↗