SQL Foundations — SELECT, WHERE

• Recognise the four parts of every query: SELECT, FROM, WHERE, semicolon.
• Pick all columns, or pick just the ones you want.
• Filter rows with =, !=, <, >, BETWEEN, IN, LIKE.
• Combine conditions with AND, OR, NOT.

Two ways to follow along — both free, both work today:

• Browser: sqliteonline.com — click "SQLite", paste & run.
• Desktop: Install DB Browser for SQLite (sqlitebrowser.org). Create a new database.

Run this once to set up our sample table:

CREATE TABLE students (
    id      INTEGER PRIMARY KEY,
    name    TEXT,
    age     INTEGER,
    class   TEXT,
    score   INTEGER
);

INSERT INTO students VALUES
  (1, 'Aisyah',  13, 'Form 1A', 88),
  (2, 'Wei Jie', 14, 'Form 1A', 75),
  (3, 'Suresh',  13, 'Form 1B', 92),
  (4, 'Mei',     14, 'Form 1B', 80),
  (5, 'Devi',    13, 'Form 1A', 65),
  (6, 'Ali',     14, 'Form 1B', 70),
  (7, 'Anita',   13, 'Form 1A', 95),
  (8, 'Razif',   14, 'Form 1B', 55);

The four parts

SELECT name, score        -- columns
  FROM students           -- table
  WHERE score >= 80       -- filter
;                         -- end of statement

name    score
Aisyah   88
Suresh   92
Mei      80
Anita    95

Picking columns

SELECT * FROM students;           -- every column
SELECT name FROM students;        -- only name
SELECT name, class FROM students; -- two columns
SELECT name AS pupil FROM students; -- rename in the result

* means "every column". Fine in a quick query; in production code, list the columns you actually need.

Comparison operators

=    equal           score = 90
!=   not equal       score != 100   (also: <>)
<    less than       age < 14
<=   less or equal   age <= 14
>    greater than    score > 80
>=   greater or eq.  score >= 80

Range and set

SELECT name FROM students WHERE score BETWEEN 70 AND 80;
SELECT name FROM students WHERE class IN ('Form 1A');
SELECT name FROM students WHERE class IN ('Form 1A', 'Form 1B');

String matching with LIKE

SELECT name FROM students WHERE name LIKE 'A%';      -- starts with A
SELECT name FROM students WHERE name LIKE '%i';      -- ends with i
SELECT name FROM students WHERE name LIKE '%e%';     -- contains e
SELECT name FROM students WHERE name LIKE '_ei%';    -- _ is exactly one char

% = any number of characters; _ = exactly one. LIKE is case-insensitive in SQLite by default for ASCII.

Combining conditions

-- Form 1A and high-scoring
SELECT name FROM students
 WHERE class = 'Form 1A' AND score >= 85;

-- Either young OR a top scorer
SELECT name FROM students
 WHERE age < 14 OR score > 90;

-- NOT
SELECT name FROM students
 WHERE NOT class = 'Form 1A';

Build a real-world query step by step. Goal: every Form 1A student aged ≤ 13 with score ≥ 80.

-- start with a sanity check: list everyone
SELECT name, age, class, score FROM students;

-- narrow to Form 1A
SELECT name, age, score FROM students WHERE class = 'Form 1A';

-- add the age cap
SELECT name, age, score FROM students
 WHERE class = 'Form 1A' AND age <= 13;

-- add the score floor
SELECT name, age, score FROM students
 WHERE class = 'Form 1A' AND age <= 13 AND score >= 80;

name    age  score
Aisyah   13    88
Anita    13    95

Read the diff

Build queries incrementally: start broad, run, narrow, run again. If a clause kills too many rows, you spot it immediately. This step-by-step rhythm is how junior engineers stop guessing.

SELECT cols FROM table WHERE condition is the shape of nearly every query. Combine conditions with AND/OR; use parentheses when you mix them. BETWEEN, IN and LIKE make filters readable. Tomorrow we add ordering and limiting.