SQL Cheatsheet

-- ── Reading ─────────────────────────────
SELECT * FROM students;
SELECT name, grade AS score FROM students;
SELECT DISTINCT age FROM students;

SELECT name FROM students
WHERE age >= 16
  AND grade > 80
  AND name LIKE 'A%'          -- % any chars, _ one char
  AND age IN (16, 17)
  AND email IS NOT NULL       -- never  = NULL
ORDER BY grade DESC, name ASC
LIMIT 10 OFFSET 20;

-- ── Aggregates ──────────────────────────
SELECT COUNT(*), AVG(grade), MAX(grade), MIN(grade), SUM(grade)
FROM students;

SELECT age, COUNT(*) AS n, AVG(grade) AS avg_grade
FROM students
WHERE grade > 0               -- WHERE: filters rows (before)
GROUP BY age
HAVING COUNT(*) > 2           -- HAVING: filters groups (after)
ORDER BY n DESC;

-- ── Joins ───────────────────────────────
SELECT s.name, c.name AS course, e.score
FROM enrollments e
JOIN students s ON s.id = e.student_id     -- inner: match both
LEFT JOIN courses c ON c.id = e.course_id; -- left: keep all left

-- rows with no match on the right:
SELECT s.name FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;

-- ── Writing ─────────────────────────────
INSERT INTO students (name, age) VALUES ('Ada', 17), ('Bob', 16);
UPDATE students SET grade = 95 WHERE id = 1;   -- ALWAYS where
DELETE FROM students WHERE id = 2;             -- ALWAYS where

-- ── Tables ──────────────────────────────
CREATE TABLE students (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE,
  age   INTEGER DEFAULT 0,
  class_id INTEGER REFERENCES classes(id)
);
ALTER TABLE students ADD COLUMN city TEXT;
DROP TABLE students;
CREATE INDEX idx_students_email ON students(email);

-- ── Useful extras ───────────────────────
SELECT name,
  CASE WHEN grade >= 90 THEN 'A'
       WHEN grade >= 80 THEN 'B'
       ELSE 'C' END AS letter
FROM students;

COALESCE(nickname, name, 'unknown')   -- first non-null

-- subquery:
SELECT name FROM students
WHERE grade > (SELECT AVG(grade) FROM students);

-- CTE — named subquery, reads top-down:
WITH top_students AS (
  SELECT * FROM students WHERE grade >= 90
)
SELECT age, COUNT(*) FROM top_students GROUP BY age;

-- transactions:
BEGIN; UPDATE ...; UPDATE ...; COMMIT;   -- or ROLLBACK;