Reference

SQL Cheat Sheet

Every syntax pattern from all 62 lessons, on one page. Bookmark this before your interview.

Basic SELECT

Select all columns

SELECT * FROM customers;

Select specific columns

SELECT first_name, city
FROM customers;

With alias

SELECT first_name AS name,
       unit_price AS price
FROM products;

Distinct values

SELECT DISTINCT city FROM customers;

Limit rows

SELECT * FROM orders LIMIT 10;

Arithmetic

SELECT product_name,
       unit_price * 1.18 AS price_with_gst
FROM products;

Filtering — WHERE

Basic filter

SELECT * FROM orders
WHERE order_status = 'Delivered';

AND / OR / NOT

SELECT * FROM customers
WHERE city = 'Seattle'
  AND loyalty_tier = 'Gold';

Comparison operators

WHERE salary > 50000
WHERE salary BETWEEN 40000 AND 70000
WHERE joined_date >= '2023-01-01'

IN operator

WHERE city IN ('Seattle', 'Austin', 'New York')

LIKE / wildcards

WHERE email LIKE '%@gmail.com'
WHERE product_name LIKE 'Horizon%'

NULL checks

WHERE delivery_date IS NULL
WHERE delivery_date IS NOT NULL

Sorting & Grouping

ORDER BY

SELECT * FROM orders
ORDER BY total_amount DESC;

ORDER BY order_date ASC, total_amount DESC;

Aggregate functions

SELECT COUNT(*) AS total_orders,
       SUM(total_amount) AS revenue,
       AVG(total_amount) AS avg_order,
       MIN(total_amount) AS smallest,
       MAX(total_amount) AS largest
FROM orders;

GROUP BY

SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city;

HAVINGFilters groups, not rows

SELECT city, COUNT(*) AS count
FROM customers
GROUP BY city
HAVING COUNT(*) > 2;

CASE WHEN

SELECT product_name,
  CASE
    WHEN unit_price < 50  THEN 'Budget'
    WHEN unit_price < 200 THEN 'Mid-range'
    ELSE 'Premium'
  END AS price_tier
FROM products;

CRUD Operations

CREATE TABLE

CREATE TABLE categories (
  category_id INTEGER PRIMARY KEY,
  name        VARCHAR NOT NULL,
  created_at  DATE DEFAULT CURRENT_DATE
);

INSERT

INSERT INTO categories (category_id, name)
VALUES (1, 'Dairy'), (2, 'Staples');

UPDATEAlways add WHERE!

UPDATE products
SET unit_price = 350
WHERE product_id = 1;

DELETEAlways add WHERE!

DELETE FROM orders
WHERE order_status = 'Cancelled';

ALTER TABLE

ALTER TABLE customers
ADD COLUMN phone_verified BOOLEAN DEFAULT false;

ALTER TABLE customers
DROP COLUMN phone_verified;

DROP / TRUNCATE

DROP TABLE temp_data;          -- removes table
TRUNCATE TABLE temp_data;      -- clears rows, keeps structure

Joins

INNER JOINOnly matching rows

SELECT c.first_name, o.order_date, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

LEFT JOINAll customers, even without orders

SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

RIGHT JOIN

SELECT c.first_name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

FULL OUTER JOIN

SELECT c.first_name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

SELF JOIN

SELECT e.first_name AS employee,
       m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Multi-table JOIN

SELECT c.first_name, o.order_date, p.product_name
FROM customers c
JOIN orders o      ON c.customer_id = o.customer_id
JOIN order_items i ON o.order_id = i.order_id
JOIN products p    ON i.product_id = p.product_id;

Subqueries & Set Ops

Subquery in WHERE

SELECT * FROM products
WHERE unit_price > (
  SELECT AVG(unit_price) FROM products
);

Subquery in FROM

SELECT city, avg_spend
FROM (
  SELECT city, AVG(total_amount) AS avg_spend
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY city
) AS city_stats
WHERE avg_spend > 1000;

EXISTS

SELECT first_name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.total_amount > 2000
);

UNION / UNION ALLUNION deduplicates; UNION ALL keeps all

SELECT city FROM customers
UNION
SELECT city FROM stores;

INTERSECT / EXCEPT

SELECT city FROM customers
INTERSECT
SELECT city FROM stores;  -- cities with both

Window Functions

ROW_NUMBER

SELECT first_name, city,
  ROW_NUMBER() OVER (
    PARTITION BY city ORDER BY joined_date
  ) AS row_in_city
FROM customers;

RANK / DENSE_RANK

SELECT product_name, unit_price,
  RANK() OVER (ORDER BY unit_price DESC) AS price_rank,
  DENSE_RANK() OVER (ORDER BY unit_price DESC) AS dense_rank
FROM products;

SUM OVER (running total)

SELECT order_date, total_amount,
  SUM(total_amount) OVER (
    ORDER BY order_date
  ) AS running_total
FROM orders;

LAG / LEAD

SELECT order_date, total_amount,
  LAG(total_amount)  OVER (ORDER BY order_date) AS prev_order,
  LEAD(total_amount) OVER (ORDER BY order_date) AS next_order
FROM orders;

CTEs & Advanced

Basic CTE

WITH bangalore_customers AS (
  SELECT * FROM customers
  WHERE city = 'Seattle'
)
SELECT * FROM bangalore_customers
WHERE loyalty_tier = 'Gold';

Multiple CTEs

WITH
  top_customers AS (
    SELECT customer_id, SUM(total_amount) AS total
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 2000
  ),
  customer_details AS (
    SELECT * FROM customers
  )
SELECT cd.first_name, tc.total
FROM top_customers tc
JOIN customer_details cd ON tc.customer_id = cd.customer_id;

Recursive CTE (hierarchy)

WITH RECURSIVE emp_hierarchy AS (
  -- Anchor: top-level managers
  SELECT employee_id, first_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: each employee's reports
  SELECT e.employee_id, e.first_name, e.manager_id, h.level + 1
  FROM employees e
  JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM emp_hierarchy ORDER BY level;

Performance

EXPLAINShows query plan

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

CREATE INDEX

CREATE INDEX idx_orders_customer
ON orders (customer_id);

CREATE INDEX idx_orders_date_status
ON orders (order_date, order_status);

Transaction

BEGIN;
  UPDATE products SET unit_price = 320 WHERE product_id = 1;
  INSERT INTO orders VALUES (...);
COMMIT;  -- or ROLLBACK if something went wrong

Projects to build

The fastest way to make this stick. Build one, then put it in your portfolio.

Beginner

FreshCart questions

Answer ten questions about the FreshCart tables with SELECT, WHERE, ORDER BY, and LIMIT. Write the question as a comment above each query.

SELECTWHEREORDER BY

Beginner

Customer rollup

One query per customer: order count, total spent, and the date of the last order. Customers with no orders still appear.

JOINGROUP BYLEFT JOIN

Intermediate

Top products by city

Rank products inside each city by revenue. Return only the top three per city.

JOINwindow functionsRANK

Intermediate

Month-over-month

Revenue by month, plus the previous month and the change, using LAG. Explain one month where the number moved.

aggregationLAGCTE

Advanced

Slow query, then an index

Take a filter-and-join query, read EXPLAIN, add one index that matches the filter, and show the plan again.

EXPLAINindexestransactions

Ready to go deeper?

Start from Lesson 01 and learn every concept with real queries and the FreshCart database.

Start Learning →