SQLDailyDrills

SQL cheat sheet (PostgreSQL)

The statements you need most, one example each. Examples run on the bakery practice database and use PostgreSQL syntax. Other databases differ in small details, which are noted where they matter.

Read and filter

SELECT chooses columns, FROM names the table, WHERE keeps matching rows, ORDER BY sorts, LIMIT cuts the result. Text goes in single quotes.

SELECT name, unit_price
FROM products
WHERE category = 'pastry' AND unit_price < 3
ORDER BY unit_price DESC
LIMIT 5;

Result: The pastries under 3.00, most expensive first.

Patterns, lists, ranges and NULL

LIKE matches patterns (% is any text, _ is one character; ILIKE ignores case). IN tests a list, BETWEEN tests an inclusive range, IS NULL finds missing values.

SELECT first_name, loyalty_tier
FROM customers
WHERE first_name LIKE 'A%' OR loyalty_tier IS NULL
ORDER BY first_name;

Result: Customers whose name starts with A, or who have no loyalty tier.

Summarise with GROUP BY

COUNT, SUM, AVG, MIN and MAX turn rows into one value per group. WHERE filters rows before grouping, HAVING filters the groups.

SELECT channel, COUNT(*) AS orders
FROM orders
GROUP BY channel
HAVING COUNT(*) > 5
ORDER BY channel;

Result: Only the online channel, with 7 orders.

Join tables

INNER JOIN keeps matches, LEFT JOIN keeps every row of the left table. Give tables short aliases and always join on the key.

SELECT p.name, SUM(oi.quantity) AS units
FROM products AS p
JOIN order_items AS oi ON oi.product_id = p.product_id
GROUP BY p.name
ORDER BY units DESC, p.name
LIMIT 3;

Result: The three best-selling products.

CASE: if/else in SQL

CASE returns the value of the first condition that is true, like IFS in Excel. Without ELSE it returns NULL.

SELECT name,
       CASE WHEN unit_price < 3 THEN 'budget' WHEN unit_price <= 4 THEN 'standard' ELSE 'premium' END AS band
FROM products
ORDER BY product_id;

Result: Each product labelled budget, standard or premium.

Subqueries and WITH

A subquery is a query inside another. WITH names one so you can read the main query like a story.

WITH spend AS (
  SELECT o.customer_id, SUM(oi.quantity * p.unit_price) AS total
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  JOIN products AS p ON p.product_id = oi.product_id
  GROUP BY o.customer_id
)
SELECT customer_id, total FROM spend WHERE total > 30 ORDER BY total DESC;

Result: Customer 1 (Lopez) with 37.10 and customer 3 (Dubois) with 36.00.

Window functions

OVER adds a calculation across rows without collapsing them: ranks, running totals, and LAG/LEAD for the previous and next row.

SELECT order_id, order_date,
       RANK() OVER (ORDER BY order_date) AS day_rank,
       LAG(order_date) OVER (ORDER BY order_id) AS prev_date
FROM orders
ORDER BY order_id;

Result: Each order with its date rank and the date of the order before.

Change data

INSERT adds rows, UPDATE changes them, DELETE removes them. Always include WHERE on UPDATE and DELETE, and run it as a SELECT first to see what it would touch. Wrap risky changes in BEGIN and ROLLBACK.

UPDATE products
SET unit_price = unit_price + 0.10
WHERE category = 'drinks'
RETURNING name, unit_price;

Result: The two drinks with their new prices.

Common questions

In which order does SQL run a query?
FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY and finally LIMIT. That is why a WHERE cannot use a column alias from SELECT, but ORDER BY can.
Is SQL case-sensitive?
Keywords and table or column names are not (PostgreSQL folds unquoted names to lower case). Text values are: 'Paris' and 'paris' differ, unless you use ILIKE or LOWER().
Does this work in MySQL, SQL Server or SQLite?
The core (SELECT, WHERE, GROUP BY, JOIN, CASE) does. Differences are in LIMIT (TOP in SQL Server), string concatenation, date functions and RETURNING. Check your database's documentation for those.

Practise it