Basics of selection I · Topic 7 Free
Pattern matching: LIKE
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.
= needs the exact value. LIKE matches text against a pattern.
The two wildcards
| Wildcard | Matches |
|---|---|
% | any sequence of characters, including nothing |
_ | exactly one character |
WHERE title LIKE 'Data%' -- starts with "Data"
WHERE title LIKE '%SQL%' -- contains "SQL" anywhere
WHERE name LIKE '%ova' -- ends with "ova"
WHERE name LIKE 'J_hn%' -- John, Jahn... one character between J and hnA pattern with no wildcards behaves like =.
Case sensitivity: ILIKE
LIKE is case-sensitive: '%engineer%' will not match 'Data Engineer'. Postgres adds ILIKE — same syntax, ignores case:
WHERE role ILIKE '%engineer%'(Standard-SQL alternative: WHERE LOWER(role) LIKE '%engineer%' — useful to know for other databases.)
NOT LIKE
Exclude a pattern:
WHERE title NOT LIKE '%SQL%'Performance note
A pattern starting with % (like '%analytics') cannot use a normal index — the database must scan every row. Fine on small tables; on billions of rows this becomes a real design question. Keep it in the back of your mind for interviews.
When patterns get more complex than “starts with / contains / ends with”, you need regular expressions — the next topic.
Practice
A real Postgres database runs in your browser. Solve the tasks, or just experiment.