SQL essentials: 50 queries and concepts

All 50 cards

1. 1
   Select active users’ names and emails
   sql

   SELECT name, email
   FROM users
   WHERE active = true;

2. 2
   WHERE vs HAVING
   WHERE filters rows before grouping; HAVING filters groups after aggregation
3. 3
   Count orders per customer
   sql

   SELECT customer_id, COUNT(*)
   FROM orders
   GROUP BY customer_id;

4. 4
   Customers with more than 5 orders
   sql

   SELECT customer_id
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 5;

5. 5
   Default direction of ORDER BY
   Ascending (ASC)
6. 6
   Paginate with LIMIT and OFFSET
   sql

   SELECT * FROM posts
   ORDER BY id
   LIMIT 20 OFFSET 40;

   Large offsets get slow; keyset pagination (WHERE id > last_id) scales better.
7. 7
   COUNT(*) vs COUNT(column)
   COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column
8. 8
   What is NULL = NULL?
   NULL (unknown), not true. Test with IS NULL / IS NOT NULL
9. 9
   Replace NULL with a default
   sql

   SELECT COALESCE(nickname, name) FROM users;

   COALESCE returns its first non-NULL argument.
10. 10
    INNER JOIN
    Only rows that have a match in both tables
11. 11
    LEFT JOIN
    Every row from the left table, with NULLs where the right table has no match
12. 12
    FULL OUTER JOIN
    Every row from both tables, with NULLs wherever one side has no match
13. 13
    CROSS JOIN
    Every combination of rows from both tables (the Cartesian product)
14. 14
    Self join example
    sql

    SELECT e.name, m.name AS manager
    FROM employees e
    LEFT JOIN employees m ON m.id = e.manager_id;

15. 15
    Find customers with no orders
    sql

    SELECT c.*
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    WHERE o.id IS NULL;

    NOT EXISTS works too.
16. 16
    Pitfall of NOT IN (subquery)
    If the subquery returns any NULL, NOT IN matches no rows; prefer NOT EXISTS
17. 17
    DISTINCT
    Removes duplicate rows from the result
18. 18
    UNION vs UNION ALL
    UNION removes duplicates; UNION ALL keeps every row and is faster
19. 19
    Wildcards in LIKE
    % matches any sequence of characters; _ matches exactly one

    sql

    WHERE email LIKE '%@example.com'

20. 20
    Conditional values with CASE
    sql

    SELECT name,
      CASE WHEN score >= 90 THEN 'A'
           WHEN score >= 80 THEN 'B'
           ELSE 'C' END AS grade
    FROM results;

21. 21
    The five standard aggregate functions
    COUNT, SUM, AVG, MIN, MAX
22. 22
    Primary key
    A column (or set of columns) that uniquely identifies each row; unique and not NULL
23. 23
    Foreign key
    A column that references a key in another table, enforcing referential integrity
24. 24
    ON DELETE CASCADE
    Deleting a parent row also deletes the rows that reference it
25. 25
    Does a UNIQUE column allow several NULLs?
    In PostgreSQL and most databases, yes: NULLs aren’t equal to each other

    SQL Server allows only one NULL in a unique index.
26. 26
    What does an index trade off?
    Faster reads and lookups for slower writes and extra storage
27. 27
    Column order in a composite index on (a, b)
    It helps queries filtering on a, or a and b, but not b alone (leftmost prefix rule)
28. 28
    EXPLAIN vs EXPLAIN ANALYZE
    EXPLAIN shows the planned execution; EXPLAIN ANALYZE runs the query and shows real timings
29. 29
    Normalization
    Structuring tables to reduce redundancy and update anomalies (1NF, 2NF, 3NF, …)
30. 30
    First normal form (1NF)
    Every column holds atomic values; no repeating groups
31. 31
    Run statements as one transaction
    sql

    BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;

32. 32
    ACID
    Atomicity, Consistency, Isolation, Durability
33. 33
    The four standard isolation levels
    Read uncommitted, Read committed, Repeatable read, Serializable
34. 34
    Dirty read
    Reading another transaction’s changes before it commits
35. 35
    Number rows per group with a window function
    sql

    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY created_at
    ) AS n
    FROM reviews;

36. 36
    RANK vs DENSE_RANK
    RANK leaves gaps after ties (1, 1, 3); DENSE_RANK doesn’t (1, 1, 2)
37. 37
    Running total
    sql

    SELECT day, SUM(amount) OVER (ORDER BY day) AS running_total
    FROM sales;

38. 38
    LAG and LEAD
    Window functions that read a value from the previous or next row
39. 39
    Common table expression (CTE)
    sql

    WITH recent AS (
      SELECT * FROM orders WHERE created_at > now() - interval '7 days'
    )
    SELECT COUNT(*) FROM recent;

40. 40
    What is WITH RECURSIVE for?
    Querying hierarchies and graphs, like a category tree or an org chart
41. 41
    Upsert in PostgreSQL
    sql

    INSERT INTO tags (name) VALUES ('sql')
    ON CONFLICT (name) DO NOTHING;

    Use DO UPDATE SET … to update the existing row instead.
42. 42
    DELETE vs TRUNCATE
    DELETE removes chosen rows one by one; TRUNCATE empties the whole table at once and is much faster
43. 43
    Add a column to a table
    sql

    ALTER TABLE users ADD COLUMN bio text;

44. 44
    How do you prevent SQL injection?
    Use parameterized queries; never concatenate user input into SQL
45. 45
    View vs materialized view
    A view runs its query each time; a materialized view stores the result until refreshed
46. 46
    Logical order a SELECT is evaluated in
    FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
47. 47
    Correlated subquery
    A subquery that refers to the outer query, so it is evaluated for each outer row
48. 48
    CHAR(n) vs VARCHAR(n)
    CHAR is fixed-length and padded; VARCHAR stores variable-length strings up to n
49. 49
    Why can’t you use a SELECT alias in WHERE?
    WHERE is evaluated before SELECT, so the alias doesn’t exist yet
50. 50
    Second-highest salary
    sql

    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1;