SQL Cheat Sheet_

SELECT id, name, email          -- pick columns
FROM users
WHERE active = true
ORDER BY name ASC               -- ASC is the default; DESC reverses
LIMIT 20 OFFSET 40;             -- page 3 of 20 (MySQL/Postgres syntax)

SELECT DISTINCT country FROM users;   -- unique values
SELECT *, now() AS fetched_at FROM users;

WHERE age >= 18 AND country IN ('US', 'CA')
WHERE name LIKE 'A%'             -- starts with A   (_ matches one char)
WHERE created_at BETWEEN '2026-01-01' AND '2026-06-30'
WHERE deleted_at IS NULL        -- never = NULL; NULL is not a value
WHERE email IS NOT NULL AND score > (SELECT avg(score) FROM users);

SELECT o.id, u.name, o.total
FROM orders o
JOIN users u   ON u.id = o.user_id        -- INNER: matches in both
LEFT JOIN coupons c ON c.order_id = o.id; -- keep all orders, NULL if no coupon

-- INNER  keeps rows with a match on both sides
-- LEFT   keeps all left rows;  RIGHT keeps all right rows
-- FULL   keeps everything;     CROSS is every combination

SELECT country,
       count(*)        AS users,
       avg(score)      AS avg_score,
       max(created_at) AS newest
FROM users
GROUP BY country                -- one row per country
HAVING count(*) > 10            -- filter groups (WHERE filters rows)
ORDER BY users DESC;

-- Common table expression: name a query, then use it like a table
WITH recent AS (
  SELECT user_id, count(*) AS n
  FROM orders
  WHERE created_at > now() - interval '30 days'
  GROUP BY user_id
)
SELECT u.name, recent.n
FROM recent
JOIN users u ON u.id = recent.user_id
WHERE recent.n > 5;

SELECT name, country, score,
       rank()       OVER (PARTITION BY country ORDER BY score DESC) AS rank_in_country,
       avg(score)   OVER (PARTITION BY country) AS country_avg,
       sum(score)   OVER (ORDER BY created_at)  AS running_total
FROM users;
-- Window functions compute across rows without collapsing them like GROUP BY.

INSERT INTO users (name, email) VALUES ('Turtle', 't@x.io');
INSERT INTO users (name, email)
  VALUES ('A', 'a@x.io'), ('B', 'b@x.io');   -- multiple rows

UPDATE users SET active = false WHERE last_seen < '2026-01-01';
DELETE FROM users WHERE active = false;

-- Always test a destructive WHERE as a SELECT first.
INSERT INTO users (email) VALUES ('t@x.io')
  ON CONFLICT (email) DO UPDATE SET name = excluded.name;   -- upsert (Postgres)

CREATE TABLE users (
  id         serial PRIMARY KEY,
  email      varchar(255) UNIQUE NOT NULL,
  name       text,
  score      numeric DEFAULT 0,
  created_at timestamp DEFAULT now()
);

ALTER TABLE users ADD COLUMN country varchar(2);
ALTER TABLE users DROP COLUMN score;
CREATE INDEX idx_users_email ON users (email);   -- speed up lookups
DROP TABLE users;

SELECT email FROM users
UNION              -- combine and dedupe;  UNION ALL keeps duplicates
SELECT email FROM subscribers;

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;            -- ROLLBACK to undo everything since BEGIN