SQL Reference Guide — Basics to Expert

Start here — if you are a student or fresher

SQL is a language for asking questions about tables of data. You describe what you want; the database figures out how to fetch it (mostly—you still learn good habits).

Words that matter

       
• Database: a collection of related data managed by software.
       
• Table: like one sheet in a spreadsheet—named columns, many rows.
       
• Row: one record (e.g. one order). Column: one field on every row (e.g. amount).
       
• Primary key: column(s) that uniquely identify a row (often an id).
     

Read a SELECT in English

Logical order to reason about (not always execution order): FROM (where is the data?) → WHERE (which rows keep?) → SELECT (which columns show?) → ORDER BY (how to sort?).

SELECT name, amount
FROM sales
WHERE region = 'EU'
ORDER BY amount DESC;

Plain English: “From the sales table, keep only rows where region is EU, show name and amount, sort biggest amount first.”

Common fresher mistakes

       
• Using = NULL instead of IS NULL—two unknowns are not equal in SQL.
       
• Mixing quotes: text uses single quotes in standard SQL ('EU'); double quotes are often for identifiers.
       
• Old-style comma joins (FROM a, b WHERE …)—prefer explicit JOIN … ON … so you do not accidentally multiply rows.
     

Basics — read and filter data

Think: FROM → WHERE → SELECT → ORDER (logical order you reason about, not always how the optimizer runs).

Core statements

       
• SELECT columns or expressions; AS renames output.
       
• FROM one table or a join of tables / subqueries.
       
• WHERE filters rows before grouping (no aggregates here in standard SQL).
       
• ORDER BY sorts; LIMIT / OFFSET cap rows (syntax varies: SQL Server uses TOP, etc.).
     

SELECT id, name, amount * 1.1 AS amount_with_tax
FROM sales
WHERE region = 'EU' AND amount > 0
ORDER BY amount DESC
LIMIT 100;

Joins (interview favourite)

Nulls and safety

       
• NULL = NULL is unknown, not true—use IS NULL / IS NOT NULL.
       
• COALESCE(a, b) first non-null; NULLIF(x, y) null if equal.
     

Intermediate — aggregates, shapes, reuse

Group and filter groups

SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
FROM sales
WHERE order_date >= DATE '2026-01-01'
GROUP BY region
HAVING SUM(amount) > 10000;

HAVING filters after aggregation (like a WHERE for groups).

Common Table Expressions (CTEs)

Readable “pipeline in SQL”: name intermediate steps. Interview win: you structure logic instead of nested subqueries only.

WITH daily AS (
  SELECT order_date::DATE AS d, SUM(amount) AS rev FROM sales GROUP BY 1
)
SELECT d, rev, AVG(rev) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rev_7d_avg
FROM daily;

CASE — conditional values

CASE WHEN amount >= 1000 THEN 'large' WHEN amount >= 100 THEN 'medium' ELSE 'small' END AS tier

Set operations

UNION combines results (dedup); UNION ALL keeps duplicates (usually faster). INTERSECT / EXCEPT where supported.

Semi-join patterns

EXISTS (SELECT 1 FROM … WHERE …) stops once a match exists—often clearer than IN (subquery) for large sets.

Expert — windows, analytics, SQL that “feels like code”

Window functions

Compute per row using a window of related rows without collapsing to one row per group. Pattern: function() OVER (PARTITION BY … ORDER BY … frame).

SELECT
  user_id,
  event_time,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS seq,
  LAG(event_type, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_type
FROM events;

       
• ROW_NUMBER() unique rank; RANK() ties skip numbers; DENSE_RANK() ties don’t skip.
       
• SUM(x) OVER (…) running totals when you set a frame (e.g. rows between unbounded preceding and current row).
     

Snowflake / modern dialect: QUALIFY

Filter on window results without a subquery—great for “latest row per key” patterns (verify support on your engine).

MERGE (upsert)

One statement: match rows, then WHEN MATCHED update/delete, WHEN NOT MATCHED insert—common in slowly changing dimensions and sync jobs.

Performance mindset (interview gold)

       
• Sargable predicates: filter on raw columns when possible; wrapping columns in functions can block index / zone-map use.
       
• Cardinality: know when nested loops vs hash join wins (conceptually).
       
• Read EXPLAIN / profile as “bytes scanned, spill, skew”—not magic.
     

Real time & pipelines (SQL’s role)

Read this first if you are new: A warehouse is built for big questions over history, not for replacing your checkout database. “Fresher data” usually means shorter gaps between batches (minutes) or continuous small loads, then SQL that only touches new or changed rows instead of re-reading everything.

“Real time” in warehouses is usually micro-batch or continuous ingest, then SQL transforms on fresh data—not single-row OLTP inside the warehouse.

Vocabulary (one line each)

       
• Watermark: a saved “we processed up to here” timestamp or ID so the next run knows what is new.
       
• Idempotency: running the same load twice does not double-count—usually natural keys + MERGE or delete-by-partition patterns.
       
• At-least-once delivery: the pipeline may deliver duplicates; your SQL must tolerate or dedupe them.
     

Why micro-batch is common

       
• Streaming engines (Kafka, Flink, …) excel at per-event windows; warehouses excel at set-based SQL—teams often meet in the middle with small files or micro-batches landing every minute.
       
• Dashboards rarely need millisecond freshness; they need correct, bounded-latency numbers and predictable cost.
       
• Full re-scans of huge history every run explode credits—incremental SQL is the cost lever.
     

Interview angles — how to sound experienced

       
• Clarify the question: engine? data size? latency SLA? idempotency?
       
• State tradeoffs: readability vs one giant query; CTE materialization behavior varies by platform.
       
• One concrete example: “We replaced a correlated subquery with a window because …”
       
• Testing: row counts, checksums, NULL rates, duplicate keys—how you’d prove correctness after a rewrite.
       
• Honesty: “I’d validate syntax in docs for this engine” beats bluffing dialect trivia.
     

Pair with SnowPro study for Snowflake-specific objects (VARIANT, streams, tasks) on top of this SQL base.

Top SQL interview questions (click to expand)

Short model answers—adapt to the engine (Postgres, SQL Server, Snowflake, etc.).

SELECT user_id, COUNT(*) AS c
FROM events
GROUP BY user_id
HAVING COUNT(*) > 1;

See also Python cheat sheet for paired data-engineering interviews.