SQL Basics

The goal: read and write 80 % of queries you’ll ever need.

1 · SELECT anatomy

SELECT column_list
FROM   table
WHERE  predicate
GROUP  BY cols
HAVING aggregate_predicate
ORDER  BY cols
LIMIT  n OFFSET m;

Only SELECT … FROM … is mandatory; everything else is optional and ordered exactly like above per spec.

1.1 · Filtering (WHERE)

SELECT * FROM orders
WHERE  status = 'shipped'
  AND  created_at >= NOW() - INTERVAL '30 days';

WHERE happens before grouping—filter early for speed.

1.2 · Aggregation (GROUP BY, HAVING)

SELECT country, COUNT(*) AS users
FROM   accounts
GROUP  BY country
HAVING COUNT(*) > 100;

HAVING filters after aggregation; ideal for “top N” style analytics.

2 · Writing Data

INSERT INTO users (id, email) VALUES (gen_random_uuid(), 'a@b.com');

UPDATE users SET last_login = NOW() WHERE id = '…';

DELETE FROM users WHERE last_login < NOW() - INTERVAL '1 year';

Wrap multiple writes in a transaction to keep data consistent.

3 · Data types you’ll meet daily

Next stop → Joins.