SQL cheat sheet

SELECT which_columns_you_want
FROM where_it_lives
WHERE which_rows_to_keep;

Pick the columns you want and drop the rows you do not. The worked answer is SELECT customer, amount FROM orders WHERE city = 'Auckland';

SELECT which_columns_you_want
FROM where_it_lives
ORDER BY what_to_sort_by
LIMIT how_many_rows;

Put the rows in an order that means something, then take the top few. The worked answer is SELECT name, revenue FROM products ORDER BY revenue DESC LIMIT 3;

SELECT DISTINCT which_columns_you_want
FROM where_it_lives
WHERE which_rows_to_keep;

Ask what values exist without being handed the same one ten times. The worked answer is SELECT DISTINCT channel FROM signups;

SELECT COUNT(*), SUM(which_column), AVG(which_column)
FROM where_it_lives
WHERE which_rows_to_include;

Turn a column of numbers into one number that answers the question. The worked answer is SELECT COUNT(*) AS tickets, COUNT(rating) AS rated FROM tickets;

SELECT what_identifies_the_group, what_to_work_out
FROM where_it_lives
GROUP BY the_same_thing_again;

One row per region instead of one per order. The worked answer is SELECT region, SUM(amount) AS total FROM orders GROUP BY region;

SELECT columns_from_either_table
FROM the_first_table
INNER JOIN the_second_table
ON what_makes_a_row_match;

Two tables, one result, matched on the column they have in common. The worked answer is SELECT customers.name, orders.amount FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

SELECT a_column,
       CASE WHEN a_test THEN a_label
            ELSE another_label
       END AS what_to_call_it
FROM where_it_lives;

Turn a number into a word people can actually read. The worked answer is SELECT customer, CASE WHEN amount >= 1000 THEN 'large' ELSE 'small' END AS size FROM orders;

SELECT which_columns_you_want
FROM where_it_lives
WHERE a_column > (SELECT one_value FROM where_it_lives);

Let one query work out the number the other one needs. The worked answer is SELECT customer, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);