Basics of selection I · Topic 5 Free
Filtering rows: WHERE
Data model — the tables you will query
How the tables join
| Relationship | Cardinality | Join condition |
|---|---|---|
| students.cohort_id → cohorts.id | many students → one cohort | JOIN cohorts ON students.cohort_id = cohorts.id |
| enrollments.student_id → students.id | many enrollments → one student | JOIN students ON enrollments.student_id = students.id |
| enrollments.course_id → courses.id | many enrollments → one course | JOIN courses ON enrollments.course_id = courses.id |
| job_offers.student_id → students.id | many job offers → one student | JOIN students ON job_offers.student_id = students.id |
In short: each student belongs to one cohort. Students enroll in courses through enrollments, and their job offers land in job_offers.
SELECT chooses columns. WHERE chooses rows. This is the most-used clause in SQL.
SELECT name, city
FROM students
WHERE country = 'Canada';The condition is checked for every row; only rows where it is true survive.
Comparison operators
| Operator | Meaning |
|---|---|
= | equal (one =, not two) |
<> or != | not equal |
> < | greater / less than |
>= <= | greater / less or equal |
They work on numbers, text, and dates:
SELECT * FROM job_offers WHERE salary_usd >= 100000;
SELECT * FROM students WHERE joined_at >= '2025-01-01';
SELECT * FROM courses WHERE difficulty <> 'beginner';Text comparison is case-sensitive: 'canada' = 'Canada' is false.
Combining conditions: AND, OR, NOT
SELECT company, role, salary_usd
FROM job_offers
WHERE city = 'Berlin' AND salary_usd > 90000;AND— both conditions must be true.OR— at least one must be true.NOT— flips a condition.
AND binds tighter than OR. Use parentheses to make mixed conditions unambiguous:
-- offers in Berlin or Toronto, and always above 90k
WHERE (city = 'Berlin' OR city = 'Toronto') AND salary_usd > 90000;Without the parentheses, this query would mean something different. When in doubt, add them.
Booleans
For boolean columns, write the condition directly:
WHERE remote -- remote offers
WHERE NOT remote -- office offers
WHERE remote = true -- also fine, more explicitPractice
A real Postgres database runs in your browser. Solve the tasks, or just experiment.