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, 'Mia',  13, 'Year 7A', 88),
  (2, 'Leo', 14, 'Year 7A', 75),
  (3, 'Victor',  13, 'Year 7B', 92),
  (4, 'Mei',     14, 'Year 7B', 80),
  (5, 'Maya',    13, 'Year 7A', 65),
  (6, 'Alex',     14, 'Year 7B', 70),
  (7, 'Anita',   13, 'Year 7A', 95),
  (8, 'Razif',   14, 'Year 7B', 55);

The four parts

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

name    score
Mia   88
Victor   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 ('Year 7A');
SELECT name FROM students WHERE class IN ('Year 7A', 'Year 7B');

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

-- Year 7A and high-scoring
SELECT name FROM students
 WHERE class = 'Year 7A' 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 = 'Year 7A';

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

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

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

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

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

name    age  score
Mia   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.