SQL cheat sheet

SELECT title, year FROM books;
SELECT * FROM books;  -- all columns

• SELECT lists the columns, with commas between them.
• FROM names the table.
• A semicolon ends the query.
• Keywords are not case-sensitive. Capital letters make them easy to see.

SELECT title, year FROM books
WHERE genre = 'Mystery' AND year >= 2020;

Text goes in single quotes. A number has no quotes.

Condition	True when
year = 2019	the values are equal
year <> 2019	the values are not equal
price < 15	less than. Also >, <=, >=.
a AND b	the two conditions are true
a OR b	one condition or the two conditions are true
NOT a	the condition is false
title LIKE '%Map%'	the text matches the pattern
year BETWEEN 2015 AND 2018	the value is in the range, with its two ends
genre IN ('Fantasy', 'Mystery')	the value is in the list

In a pattern, % is any number of characters, and _ is one character. Use parentheses when you mix AND and OR.

-- From small to large
SELECT title, price FROM books ORDER BY price;

-- From large to small
SELECT title FROM books ORDER BY price DESC;

-- By genre, and by title in the same genre
SELECT genre, title FROM books
ORDER BY genre, title;

-- The 2 newest books
SELECT title FROM books
ORDER BY year DESC LIMIT 2;

-- Each author one time
SELECT DISTINCT author FROM books;

A query promises no order without ORDER BY.

Function	Result
COUNT(*)	the number of rows
SUM(price)	the total of the values
AVG(price)	the average of the values
MIN(price)	the smallest value
MAX(price)	the largest value
ROUND(value, 1)	the value with 1 decimal

SELECT COUNT(*) AS book_count FROM books;

SELECT ROUND(AVG(price), 1) AS average_price
FROM books
WHERE genre = 'Fantasy';

• An aggregate function reads many rows and gives one value.
• AS gives a column a name, an alias.
• WHERE runs first. Only the rows that match are counted.

SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING COUNT(*) >= 2;

• GROUP BY makes one group for each value. The function runs one time for each group.
• Each column after SELECT is in the GROUP BY clause or in an aggregate function.
• WHERE removes rows before the groups are made. HAVING removes groups after that.

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
7. LIMIT

Leave out the clauses that you do not need. Keep the others in this order.

-- Only the customers that have orders
SELECT c.name, o.item
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;

-- All customers, also those with no orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.name;

• ON says which rows of the two tables belong together.
• A foreign key, such as customer_id, holds the id of a row in another table.
• A LEFT JOIN keeps each row of the first table. With no match, the other columns are NULL.
• COUNT(o.id) does not count a NULL. COUNT(*) counts each row.

CREATE TABLE pets (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER
);

INSERT INTO pets (id, name, age)
VALUES (1, 'Biscuit', 4), (2, 'Pepper', 2);

UPDATE pets SET age = 5 WHERE name = 'Biscuit';

DELETE FROM pets WHERE age > 10;

• Types: INTEGER is a whole number, TEXT is text, REAL is a number with decimals.
• PRIMARY KEY marks the column that identifies each row.
• NOT NULL means that the column always has a value.
• UPDATE and DELETE without WHERE change every row of the table.

-- One value: compare with it
SELECT title, year FROM books
WHERE year > (SELECT AVG(year) FROM books);

-- A list of values: use IN or NOT IN
SELECT title FROM books
WHERE genre IN (
  SELECT genre FROM books WHERE price > 20
);

A subquery is a SELECT in parentheses in another query. The database runs it first. Write the inner query alone and run it. Then write the outer query around it.