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

Module M04: Databases & SQL · Session 36 of 130 · Track: DE 🔤

What you'll be able to do after this session

• Explain SQL Basics to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S035 — The Relational Model — Tables, Keys, Normalisation

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · SQL in 100 Seconds — Fireship — fastest intro; nails SELECT/WHERE/ORDER BY.
• 🎥 Deep dive (30–60 min) · Stanford DB Course — Intro to SQL (Jennifer Widom) — the gold-standard university intro; watch parts 1–3.
• 🎥 Hands-on demo (10–20 min) · freeCodeCamp — SQL Tutorial for Beginners (first 20 min) — types real queries against a real DB.
• 📖 Canonical article · PostgreSQL Docs — The SQL Language (Tutorial Ch. 2) — official reference-grade tutorial.
• 📖 Book chapter / tutorial · Mode Analytics — SQL Tutorial (Basic) — interactive; type queries in the browser against real tables.
• 📖 Engineering blog · Stack Overflow — How We Do App Caching — practical look at how one of the world's largest sites uses raw SQL, not ORMs.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: The universal data language.

SQL is what you'd invent if you tried to describe "give me a filtered list from a table" in English and refused to write a for loop. Instead of how to fetch data (open file, iterate rows, check condition, sort, take first 10), you tell the database what you want (SELECT name FROM users WHERE age > 30 ORDER BY name LIMIT 10) and let it figure out the fastest execution. That's why SQL has outlived a dozen programming trends since 1974: declarative + relational + a query planner is the right level of abstraction for tabular data.

The four verbs you'll use 90% of the time — SELECT (which columns), FROM (which table), WHERE (which rows), ORDER BY (sort), LIMIT (top-N) — compose to answer nearly any single-table question. Add JOIN (next session) and you've unlocked every business analytics question ever asked. Same syntax works on your laptop's SQLite, Postgres running your startup, Snowflake at petabyte scale, and BigQuery. Learn it once — use it forever.

The forever gotcha: SQL keywords are executed in a different order from how they're written. The written order is SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY / LIMIT, but the execution order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That's why you can't reference a SELECT alias in WHERE — the WHERE runs first.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Toy table employees (we'll reuse this across sessions):

Query 1 — everyone in Eng earning over ₹100k, sorted, top 2:

SELECT name, salary
FROM   employees
WHERE  dept = 'Eng' AND salary > 100000
ORDER  BY salary DESC
LIMIT  2;

Execution flow:

Result:

Query 2 — count of employees per dept, alphabetical: (previews the next few sessions)

SELECT dept, COUNT(*) AS headcount
FROM   employees
GROUP BY dept
ORDER BY dept;

Cheat-sheet — operators you'll see in WHERE:

Three tiny rules that separate good from bad SQL:

1. Never SELECT * in production code. It couples your app to schema changes and reads more bytes than needed.
2. Always alias tables and complex columns with AS — SELECT SUM(price * qty) AS revenue.
3. Prefer IS NULL over = NULL. Three-valued logic (True/False/Unknown) means NULL = NULL returns NULL, not True. Missed this once = wrong report forever.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

SQLite ships with Python — no install needed. Save as sql_basics.py:

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 NOT NULL,
    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'); -- missing salary
""")
 
def run(label, sql):
    print(f"\n-- {label} --\n{sql.strip()}")
    for row in cur.execute(sql):
        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. Range + IN + LIKE
run("Salary between 80k and 120k, HR or Sales, name starts with vowel",
    """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%';""")   # note precedence bug — see checklist
 
# 3. NULL handling — the classic trap
run("Employees with UNKNOWN salary",
    "SELECT name FROM employees WHERE salary IS NULL;")
 
run("Employees where salary != 60000 (does Grace appear?)",
    "SELECT name, salary FROM employees WHERE salary != 60000;")
 
# 4. Distinct + ordering + limit-offset (pagination)
run("Distinct depts, alphabetical",
    "SELECT DISTINCT dept FROM employees ORDER BY dept;")
 
run("Page 2 of employees, 3 per page (offset 3, limit 3)",
    "SELECT id, name FROM employees ORDER BY id LIMIT 3 OFFSET 3;")
 
# 5. Query planner peek — EXPLAIN
run("EXPLAIN plan for the top-earners query",
    """EXPLAIN QUERY PLAN
       SELECT name FROM employees WHERE dept='Eng' ORDER BY salary DESC LIMIT 2;""")
 
conn.close()

Checklist — observe when you run:

1. Query 1 returns exactly 2 rows (Dan, Alice) — LIMIT cuts after sort.
2. Query 2 has an intentional bug — AND binds tighter than OR, so it reads as (BETWEEN ... AND IN ... AND LIKE 'A%') OR (LIKE 'E%'). Add parentheses around the OR to fix. This is the #1 SQL bug in production dashboards.
3. Query 3 correctly finds Grace via IS NULL. Try WHERE salary = NULL — returns zero rows.
4. Query 4 (!= 60000) does not include Grace — because NULL != 60000 evaluates to NULL, not TRUE. NULL semantics are three-valued logic; internalise this.
5. LIMIT/OFFSET is how every "page 2 of results" query works. For large offsets, prefer keyset pagination (WHERE id > last_seen_id LIMIT 10).
6. EXPLAIN QUERY PLAN shows SQLite scans the whole table — because we have no index. Add CREATE INDEX ix_dept_salary ON employees(dept, salary DESC); and re-run — plan changes.

Try this modification: add a city column, insert Bangalore / Mumbai / Hyderabad values, then write a query returning the highest-paid employee per city (hint: needs ORDER BY city, salary DESC — a real top-N-per-group needs window functions from S039).

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

SQL is the assembly language of data. Every ORM (Django ORM, SQLAlchemy, Prisma, Hibernate) eventually emits SQL. Every BI tool (Tableau, Looker, Power BI, Metabase) writes SQL. Every data warehouse (Snowflake, BigQuery, Redshift, Databricks SQL) is a SQL engine. You cannot escape it, so learn it directly rather than through an ORM.

War story — SELECT * at scale. A fintech dashboard did SELECT * FROM transactions WHERE user_id = ? for each user profile page. Innocent. Then a data engineer added a 2 KB raw_response_json column to the table. The profile page grew from 20 KB per response to 4 MB, the DB became network-bound, and P99 latency jumped from 40ms to 1200ms. Fix: enumerate the columns you actually need. Cost of fix: one PR. Cost of the outage: 3 hours + incident review.

War story — the NULL in an aggregate. A payments team ran SELECT SUM(refund_amount) FROM orders WHERE status = 'refunded'. Refund amount was NULL for orders that used store credit. SUM silently ignores NULLs — the report was off by 8% for a year. Fix: SUM(COALESCE(refund_amount, 0)) and add a NOT NULL constraint going forward.

Gotchas top teams handle:

• Never string-concat user input into SQL. SQL injection is still the #1 web vulnerability (OWASP Top 10). Use parameterised queries (cur.execute("... WHERE id = ?", (uid,))). Every driver supports this; every ORM does it by default.
• Pagination with OFFSET on large tables is slow. LIMIT 20 OFFSET 100000 still scans 100,020 rows. Use keyset pagination: WHERE id > last_seen_id ORDER BY id LIMIT 20. Facebook / Instagram feeds work this way.
• Case sensitivity. dept = 'eng' vs 'Eng' — Postgres is case-sensitive on values by default. Use ILIKE or LOWER(dept) = LOWER(?).
• Implicit type conversion. WHERE user_id = '42' (string vs int) can prevent index use in some databases. Match your types.
• SELECT COUNT(*) on billion-row tables in Postgres has to scan all rows for MVCC reasons — use pg_class.reltuples for an estimate.

The senior-engineer tell: they run EXPLAIN (or EXPLAIN ANALYZE) on every query touching more than 10k rows before merging it, and they never SELECT * in application code.

(e) Quiz + exercise · 10 min

1. State the actual execution order of SELECT / FROM / WHERE / ORDER BY / LIMIT.
2. Why does WHERE salary = NULL return zero rows even when a row has a null salary?
3. Give one reason to avoid SELECT * in production code.
4. Write a SQL query to return the 3 most recently hired employees.
5. What does LIMIT 10 OFFSET 20 mean, and when is OFFSET a performance problem?
6. Stretch (connects to S035 Relational Model): the employees table above stores dept as a text column. Refactor into a depts (dept_id PK, name) table + employees.dept_id FK. Rewrite Query 1 with a JOIN (peek at next session).

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is SQL Basics? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S037): Joins — INNER, LEFT, RIGHT, FULL, Anti-Join
• Previous (S035): The Relational Model — Tables, Keys, Normalisation
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.