Reference

SQL Cheat Sheet

Every syntax pattern from all 62 modules, 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 = 'Bangalore'
  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 ('Bangalore', 'Hyderabad', 'Mumbai')

LIKE / wildcards

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

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 = 'Bangalore'
)
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

Ready to go deeper?

Start from Module 01 and learn every concept with real queries and the FreshMart database.

Start Learning →