S036 · SQL Basics — SELECT, WHERE, ORDER BY, LIMIT

"""sql_basics.py — SELECT / WHERE / ORDER BY / LIMIT + the classic NULL gotchas."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    dept   TEXT NOT NULL,
    salary INTEGER,                  -- nullable ON PURPOSE for demo
    hired  TEXT NOT NULL
);
INSERT INTO employees VALUES
    (1, 'Alice', 'Eng',   120000, '2020-03-01'),
    (2, 'Bob',   'Eng',    95000, '2021-06-15'),
    (3, 'Carol', 'HR',     80000, '2019-01-10'),
    (4, 'Dan',   'Eng',   140000, '2018-09-20'),
    (5, 'Eve',   'Sales', 110000, '2022-02-01'),
    (6, 'Frank', 'Sales',  60000, '2023-11-05'),
    (7, 'Grace', 'HR',      NULL, '2024-05-12');
""")
 
def run(label: str, sql: str, params: tuple = ()) -> None:
    print(f"\n-- {label} --")
    print(sql.strip())
    for row in cur.execute(sql, params):
        print(" ", row)
 
# 1. Basic SELECT + WHERE + ORDER BY + LIMIT
run("Top 2 Eng earners over 100k",
    """SELECT name, salary FROM employees
       WHERE dept = 'Eng' AND salary > 100000
       ORDER BY salary DESC LIMIT 2;""")
 
# 2. Precedence bug — AND binds tighter than OR
run("Precedence BUG · name starts with A or E, in HR or Sales, 80k-120k",
    """SELECT name, dept, salary FROM employees
       WHERE salary BETWEEN 80000 AND 120000
         AND dept IN ('HR','Sales')
         AND name LIKE 'A%' OR name LIKE 'E%';""")
 
# Same query with parentheses — the intended answer
run("Precedence FIXED · with parentheses around the OR",
    """SELECT name, dept, salary FROM employees
       WHERE salary BETWEEN 80000 AND 120000
         AND dept IN ('HR','Sales')
         AND (name LIKE 'A%' OR name LIKE 'E%');""")
 
# 3. NULL handling — the classic trap
run("Employees with UNKNOWN salary (IS NULL)",
    "SELECT name FROM employees WHERE salary IS NULL;")
 
run("Wrong way: `= NULL` returns zero rows (silent bug)",
    "SELECT name FROM employees WHERE salary = NULL;")
 
run("`salary != 60000` — does Grace appear? (three-valued logic)",
    "SELECT name, salary FROM employees WHERE salary != 60000;")
 
run("`salary != 60000 OR salary IS NULL` — explicit NULL handling",
    "SELECT name, salary FROM employees WHERE salary != 60000 OR salary IS NULL;")
 
# 4. COALESCE for a default
run("Show salary as 0 when NULL, sort ascending",
    "SELECT name, COALESCE(salary, 0) AS salary FROM employees ORDER BY salary ASC;")
 
# 5. DISTINCT + LIMIT/OFFSET pagination
run("Distinct departments alphabetically",
    "SELECT DISTINCT dept FROM employees ORDER BY dept;")
 
run("Page 2 of employees, 3 per page (LIMIT 3 OFFSET 3)",
    "SELECT id, name FROM employees ORDER BY id LIMIT 3 OFFSET 3;")
 
# 6. Keyset pagination — the O(log n) way
run("Keyset pagination — next page after id=3",
    "SELECT id, name FROM employees WHERE id > ? ORDER BY id LIMIT 3;", (3,))
 
# 7. Parameterised query — the ANTI-SQL-INJECTION pattern
run("Safe: parameterised query", "SELECT name FROM employees WHERE dept = ?", ('Eng',))
 
# 8. EXPLAIN — see what the planner is doing
run("EXPLAIN plan for the top-earners query",
    """EXPLAIN QUERY PLAN
       SELECT name FROM employees
       WHERE dept='Eng' ORDER BY salary DESC LIMIT 2;""")
 
# Add an index and re-EXPLAIN
cur.execute("CREATE INDEX ix_dept_salary ON employees(dept, salary DESC)")
run("EXPLAIN after CREATE INDEX",
    """EXPLAIN QUERY PLAN
       SELECT name FROM employees
       WHERE dept='Eng' ORDER BY salary DESC LIMIT 2;""")
 
conn.close()