SQL Foundations Lesson 3 of 7

SQL WHERE: filtering rows

25 min, 4 exercisesOCR 2.2.3AQA 3.7.2

Builds on lessons 1 to 2. Start at lesson 1

Learn

Filtering with WHERE

SELECT chooses columns. WHERE chooses rows. It goes after FROM and holds a condition that each row either passes or fails:

SELECT name, score FROM students WHERE score >= 80;

The database checks every row: is this score at least 80? Yes, it goes in the result. No, it is skipped. The table itself does not change.

The comparison operators:

  • = equal (just one = in SQL, not two)
  • <> or != not equal
  • < less than, > greater than
  • <= at most, >= at least

Numbers are written as they are. Text needs single quotes:

SELECT name FROM students WHERE club = 'Chess';

Learn

Quotes and exact text

Without the quotes, SQL thinks Chess is the name of a column, can't find it, and complains. Double quotes are a trap here too: in SQL they are meant for names of columns and tables, not for text, so stick to single quotes for values.

Text comparisons with = are exact. In SQLite, 'chess' and 'Chess' are different values, and so are 'Chess' and 'Chess ' with a sneaky space on the end. If a query you are sure is right returns nothing, check the spelling and capitals of the value first.

Learn

Filtering on hidden columns

You can still pick whatever columns you like. The WHERE column does not have to be one of the columns you return:

SELECT name FROM students WHERE year = 10;

That gives you a list of names, filtered by a column you never show. The order is always SELECT ... FROM ... WHERE ..., and swapping them around is a syntax error.

GCSE exam boards
  • OCR 2.2.3 Strings, files, arrays, sub programs, SQL
  • AQA 3.7.2 SQL

GCSE Python path The GCSE lesson

Try

Run it: high scoring students

The query in the editor runs against this lesson's tables (open Tables above the editor to see them). Run it, change the WHERE or the columns, run it again.

Tables students, matches
students 9 rows, 5 columns
idnameyearclubscore
1Aisha10Chess91
2Ben11Drama64
3Chloe10Robotics78
4Dev12Chess85
5Ella11Robotics99
6Finn10Drama52
7Grace12Esports73
8Hugo11Esports88
9Ivy12Robotics60
matches 6 rows, 5 columns
idhomeawayhome_goalsaway_goals
1Red LionsKickabouts31
2Grass Roots FCThe Volley Llamas22
3KickaboutsGrass Roots FC04
4The Volley LlamasRed Lions10
5Red LionsGrass Roots FC23
6KickaboutsThe Volley Llamas52
Show the SQL that built them
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, year INTEGER, club TEXT, score INTEGER);
INSERT INTO students VALUES
 (1, 'Aisha', 10, 'Chess', 91),
 (2, 'Ben', 11, 'Drama', 64),
 (3, 'Chloe', 10, 'Robotics', 78),
 (4, 'Dev', 12, 'Chess', 85),
 (5, 'Ella', 11, 'Robotics', 99),
 (6, 'Finn', 10, 'Drama', 52),
 (7, 'Grace', 12, 'Esports', 73),
 (8, 'Hugo', 11, 'Esports', 88),
 (9, 'Ivy', 12, 'Robotics', 60);
CREATE TABLE matches (id INTEGER PRIMARY KEY, home TEXT, away TEXT, home_goals INTEGER, away_goals INTEGER);
INSERT INTO matches VALUES
 (1, 'Red Lions', 'Kickabouts', 3, 1),
 (2, 'Grass Roots FC', 'The Volley Llamas', 2, 2),
 (3, 'Kickabouts', 'Grass Roots FC', 0, 4),
 (4, 'The Volley Llamas', 'Red Lions', 1, 0),
 (5, 'Red Lions', 'Grass Roots FC', 2, 3),
 (6, 'Kickabouts', 'The Volley Llamas', 5, 2);
example.sql to run
-- Only students with a score of 80 or more
SELECT name, club, score
FROM students
WHERE score >= 80;

Practice

Find Robotics club students

Return the name and year of every student whose club is Robotics.

exercise_1.sql to run
SELECT name, year
FROM students;

Practice

Everyone but year 12

Return the name and year of every student who is not in year 12.

exercise_2.sql to run
SELECT name, year
FROM students
WHERE year = 12;

Break/fixFix the bug

Drama is not a column

This should list the names of everyone in the Drama club. It fails with an error about a column. Fix it.

exercise_3.sql to run
SELECT name
FROM students
WHERE club = Drama;

Your turn

Home wins

A football league keeps its results in a second table, matches. Return home, away, home_goals and away_goals for every match the home team won.

On your own. Hints open after your first check.

exercise_4.sql to run
-- Your query here
SQLite