Quiz
Warm-up from lesson 1-1: you built a pets table with columns name, species, age. Which query prints every column of every row?
Filtering rows
In lesson 1-2 you chose columns. Now you will choose rows, and this is the skill you will use most. A production table holds millions of rows, and almost every query a real app runs wants just a few of them: logging you in is WHERE email = ..., opening your cart is WHERE user_id = .... The WHERE clause keeps only the rows that pass a test:
SELECT title, year FROM movies WHERE year >= 2015;
The database checks the test against every row. Rows where it is true stay, the rest are dropped. The comparison operators are the ones from math:
| Operator | Meaning |
|---|---|
= | equal (one sign, not two) |
!= | not equal (also written <>) |
> < | greater / less than |
>= <= | at least / at most |
This unit uses a movies table with columns title, year, rating, genre. Run the block below and check that only movies from 2015 or later come back.
Code exercise · sql
Run it. Five movies go in, but WHERE year >= 2015 keeps only three rows.
Comparing text
WHERE works on text columns too. Use = with the value in single quotes:
SELECT title FROM movies WHERE genre = 'scifi';
Two details that trip beginners:
- SQL uses a single
=for comparison (unlike==in Python or JavaScript). - Text comparisons are exact about spelling:
'scifi'will not match'Sci-Fi'. Keep your data consistent, or useLIKE(lesson 2-3) for looser matching.
Code exercise · sql
Your turn. Change the WHERE clause so the query returns the title and rating of every movie rated above 8. Three rows should come back.
Code exercise · sql
Your turn. Print only the titles of the sci-fi movies. Remember: text values are compared with a single = and need single quotes.