A little help with the everyday.

Choose columns and filter rows.

SELECT name, score
FROM students
WHERE score >= 80;

Sort a result and return a limited number of rows.

SELECT title, price
FROM resources
ORDER BY price DESC, title ASC
LIMIT 10;

Aggregate records, then filter the grouped result.

SELECT category, COUNT(*) AS total
FROM resources
GROUP BY category
HAVING COUNT(*) >= 5;

Return records that match in both tables.

SELECT orders.id, users.name
FROM orders
INNER JOIN users ON users.id = orders.user_id;

Keep every record from the left table, even without a match.

SELECT users.name, orders.id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Label a value using conditional logic.

SELECT title,
  CASE WHEN price = 0 THEN 'Free' ELSE 'Paid' END AS tier
FROM resources;

Test for missing values or provide a fallback.

SELECT COALESCE(display_name, 'Reader') AS name
FROM users
WHERE deleted_at IS NULL;

Name a query result so you can reuse it in the following query.

WITH totals AS (
  SELECT user_id, SUM(amount) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT * FROM totals WHERE spent > 1000;

Assign a position within each group using a window function.

SELECT title, category,
  ROW_NUMBER() OVER (
    PARTITION BY category ORDER BY price DESC
  ) AS position
FROM resources;

Remove duplicate values from a result.

SELECT DISTINCT category
FROM resources
ORDER BY category;

Find rows with a related record without duplicating the result.

SELECT users.name
FROM users
WHERE EXISTS (
  SELECT 1 FROM orders
  WHERE orders.user_id = users.id
);

Combine compatible results while retaining duplicates.

SELECT title FROM resources
UNION ALL
SELECT title FROM archived_resources;