DBMS

SQL Fundamentals

Querying with SELECT

SELECT name, city, marks -- π which columns

JrCodex·7 min read

Jr Codex DBMS Notes

Level: Beginner Prerequisites: Chapter 1: DDL — Defining Schema Time to complete: ~20 minutes

Table of Contents

  1. The Shape of a Query
  2. Filtering with WHERE
  3. Pattern Matching and Ranges
  4. Handling NULL
  5. Expressions and CASE
  6. Sorting and Limiting
  7. Summary & Next Steps

1. The Shape of a Query

SELECT   name, city, marks        -- π  which columns
FROM     students                 --    which relation
WHERE    marks > 80               -- σ  which rows
ORDER BY marks DESC               --    presentation only
LIMIT    3;                       --    presentation only
Two Habits to Form Now
─────────────────────────────────────────
  NAME YOUR COLUMNS. SELECT * is fine when
  exploring and a liability in code — it breaks
  when a column is added, fetches data you do not
  need, and prevents index-only scans (Module 5).

  ORDER BY IS NOT OPTIONAL when order matters.
  Relations are unordered (Module 2, Chapter 1);
  without ORDER BY the database may return rows in
  any order, and that order can change when the
  plan changes.
─────────────────────────────────────────

2. Filtering with WHERE

SELECT name, marks FROM students WHERE city = 'Pune';
SELECT name, marks FROM students WHERE marks >= 80;
SELECT name FROM students WHERE city = 'Pune' AND marks > 85;
SELECT name FROM students WHERE city = 'Delhi' OR city = 'Mumbai';
SELECT name FROM students WHERE NOT city = 'Pune';
Operator Precedence
─────────────────────────────────────────
  NOT binds tighter than AND, which binds tighter
  than OR.

  So this:
    WHERE city = 'Pune' AND marks > 85 OR marks > 90

  Means this:
    WHERE (city = 'Pune' AND marks > 85) OR marks > 90

  Which probably is not what you meant. PARENTHESISE
  whenever AND and OR appear together — always, not
  only when unsure.
─────────────────────────────────────────

-- Comparison operators
=   <>   !=   <   >   <=   >=
 
-- Set membership: IN is cleaner than a chain of ORs
SELECT name FROM students WHERE city IN ('Delhi','Mumbai');
SELECT name FROM students WHERE city NOT IN ('Pune');

3. Pattern Matching and Ranges

-- LIKE: % matches any sequence, _ matches exactly one character
SELECT name FROM students WHERE name LIKE 'A%';      -- starts with A
SELECT name FROM students WHERE name LIKE '%a';      -- ends with a
SELECT name FROM students WHERE name LIKE '%ee%';    -- contains 'ee'
SELECT name FROM students WHERE name LIKE '_a%';     -- 'a' as 2nd character
 
-- BETWEEN is inclusive at BOTH ends
SELECT name, marks FROM students WHERE marks BETWEEN 70 AND 85;
-- identical to: marks >= 70 AND marks <= 85
The LIKE Performance Trap
─────────────────────────────────────────
  LIKE 'A%'     CAN use an index — the prefix is
                known, so the B+ tree can seek to
                it (Module 5, Chapter 2).

  LIKE '%son'   CANNOT. A leading wildcard means
                every row must be examined.

  For substring search at scale you need a
  full-text index or a trigram index — not LIKE.
  This single fact explains a lot of slow search
  boxes.
─────────────────────────────────────────

BETWEEN and Dates
─────────────────────────────────────────
  BETWEEN '2026-01-01' AND '2026-01-31' on a
  TIMESTAMP silently excludes almost all of the
  31st — because '2026-01-31' means midnight at
  its start.

  Use a half-open range instead:
    WHERE ts >= '2026-01-01' AND ts < '2026-02-01'

  Half-open ranges also compose cleanly across
  month boundaries, which BETWEEN does not.
─────────────────────────────────────────

4. Handling NULL

Module 2, Chapter 1 covered the theory. This is the practice.

SELECT name FROM students WHERE marks IS NULL;        -- Sam
SELECT name FROM students WHERE marks IS NOT NULL;    -- the other five
 
SELECT name FROM students WHERE marks = NULL;         -- ALWAYS empty. Never write this.
The Exclusion Trap
─────────────────────────────────────────
  SELECT name FROM students WHERE marks < 80;

  Returns Ravi and Karan — but NOT Sam, whose marks
  are NULL. Sam is neither below 80 nor at-or-above
  it; the comparison is UNKNOWN and the row is
  dropped.

  If "not yet assessed" should appear in a
  low-scorers report, you must ask for it:

    WHERE marks < 80 OR marks IS NULL
─────────────────────────────────────────

-- COALESCE: first non-null argument
SELECT name, COALESCE(marks, 0) AS marks_or_zero FROM students;
SELECT name, COALESCE(city, 'Unknown') AS city FROM students;
 
-- NULLIF: NULL when the two arguments are equal — useful to avoid divide-by-zero
SELECT total / NULLIF(count_of_items, 0) AS average FROM sales;
Displaying vs Filtering
─────────────────────────────────────────
  COALESCE is for DISPLAY — turning NULL into
  something readable in the output.

  It is not a substitute for handling NULL in
  WHERE. Wrapping a column in COALESCE inside a
  WHERE clause also prevents the index on that
  column from being used (Module 5, Chapter 4).
─────────────────────────────────────────

5. Expressions and CASE

-- Arithmetic and derived columns
SELECT name, marks, marks * 1.1 AS boosted FROM students;
SELECT name || ' (' || COALESCE(city,'?') || ')' AS label FROM students;
 
-- CASE: SQL's if/else
SELECT name,
       marks,
       CASE
           WHEN marks IS NULL THEN 'Not assessed'
           WHEN marks >= 85   THEN 'Distinction'
           WHEN marks >= 70   THEN 'Pass'
           ELSE                    'Needs work'
       END AS band
FROM students
ORDER BY marks DESC NULLS LAST;
CASE Evaluates Top-Down
─────────────────────────────────────────
  The FIRST matching WHEN wins, so order matters.

  Putting `WHEN marks >= 70` before
  `WHEN marks >= 85` would band everyone above 85
  as 'Pass', because the first condition already
  matched.

  Also: put the NULL check FIRST. `marks >= 85`
  is UNKNOWN for NULL, so it falls through to
  ELSE and Sam would be labelled 'Needs work'
  rather than 'Not assessed'.
─────────────────────────────────────────

6. Sorting and Limiting

SELECT name, marks FROM students ORDER BY marks DESC;
SELECT name, city, marks FROM students ORDER BY city ASC, marks DESC;   -- two keys
 
-- NULL placement is explicit in standard SQL
SELECT name, marks FROM students ORDER BY marks DESC NULLS LAST;
 
-- Paging
SELECT name, marks FROM students ORDER BY marks DESC LIMIT 3;
SELECT name, marks FROM students ORDER BY marks DESC LIMIT 3 OFFSET 3;   -- page 2
Ties Make Paging Unstable
─────────────────────────────────────────
  If several rows share the sort value, their
  relative order is UNDEFINED and may differ
  between runs.

  Page 1 and page 2 can then both contain the same
  row, and another row appears on neither.

  FIX: always add a unique tie-breaker.
    ORDER BY marks DESC, id ASC

  This is a real, common, hard-to-reproduce bug in
  paginated APIs.
─────────────────────────────────────────

OFFSET Does Not Scale
─────────────────────────────────────────
  LIMIT 20 OFFSET 100000 makes the database
  produce 100,020 rows and discard 100,000 of them.
  Deep pages get linearly slower.

  KEYSET PAGINATION instead — remember the last row
  and seek past it:

    SELECT ... WHERE (marks, id) < (:last_marks, :last_id)
    ORDER BY marks DESC, id DESC LIMIT 20;

  Constant time at any depth, because the index
  seeks directly (Module 5).
─────────────────────────────────────────

7. Summary & Next Steps

Key Takeaways

  • Name your columns rather than using SELECT *, and never rely on row order without an explicit ORDER BY.
  • Parenthesise whenever AND and OR appear together — NOT binds tightest, then AND, then OR.
  • A leading wildcard in LIKE makes an index unusable, and BETWEEN on timestamps silently loses the last day; use half-open ranges.
  • Comparisons never match NULL, so exclusion filters silently drop those rows; add OR col IS NULL when they belong in the result.

Concept Check

  1. WHERE marks < 80 omits a student. Which one, why, and what is the fix?
  2. Why does LIKE '%son' force a full scan while LIKE 'A%' does not?
  3. A paginated API occasionally shows the same row on two consecutive pages. What is the cause and the one-line fix?

Next Chapter

→ Chapter 3: Joins

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index