Skip to content

exercises.md

Latest commit

 

History

History
239 lines (131 loc) · 6.14 KB

exercises.md

File metadata and controls

239 lines (131 loc) · 6.14 KB

Section 2 — Filtering Data

What you'll practice: cutting a big result set down to exactly the rows you want with WHERE, then layering on comparison operators (=, <, >, <>), logical operators (AND, OR, NOT), set membership (IN), range checks (BETWEEN), pattern matching (LIKE / ILIKE), null-handling (IS NULL), and pagination (LIMIT / OFFSET).

Learning objectives

  • Use WHERE with comparison operators to filter rows
  • Combine conditions with AND, OR, and parentheses
  • Write set-membership filters with IN and NOT IN
  • Use BETWEEN for inclusive range filters
  • Match text patterns with LIKE and case-insensitive ILIKE
  • Handle NULL correctly with IS NULL / IS NOT NULL
  • Paginate results with LIMIT / OFFSET (and the SQL-standard FETCH FIRST ... ROWS ONLY)

How to run these exercises

psql -U postgres -d sql_exercise \
     -f 02-sql-fundamentals/02-filtering-data/schema.sql

psql -U postgres -d sql_exercise \
     -f 02-sql-fundamentals/02-filtering-data/seed.sql

Then from your psql prompt:

SET search_path TO sqlf_filtering;
SELECT COUNT(*) FROM person;  -- expect 50

Exercises

Exercise 1 — Everyone from the USA

Return every column for people whose country_of_origin is 'USA'.

Verify: eyeball the country column — they should all read USA.

Exercise 2 — Everyone who is female

Return first_name, last_name, and country_of_origin for every female person.

Hint: gender = 'F'.

Exercise 3 — Not from the USA

Return everyone whose country_of_origin is not 'USA'.

Hint: the "not equal" operator in SQL is <> (or != in most dialects — both work in Postgres).

Exercise 4 — Born after 1990

Return first_name, last_name, and dob for every person born strictly after 1990-01-01.

Question: does dob > '1990-01-01' include someone born on 1990-01-01? Why not?

Exercise 5 — Born in 1995 or later

Return everyone born on or after 1995-01-01.

Hint: >= is the "greater than or equal" operator.

Exercise 6 — Combined filter: female + from Egypt

Return every female person whose country_of_origin is 'Egypt'.

Hint: WHERE gender = 'F' AND country_of_origin = 'Egypt'.

Exercise 7 — OR condition

Return everyone from either 'UK' or 'Canada'.

Bonus: do this with OR first, then rewrite it using IN — see exercise 9.

Exercise 8 — Parentheses matter

Return every female person from Egypt or Morocco.

Hint: without parentheses, WHERE gender = 'F' AND country_of_origin = 'Egypt' OR country_of_origin = 'Morocco' does not mean what you think. Wrap the OR in parentheses.

Verify: every row should have gender = 'F' and be from Egypt or Morocco — never a male from Morocco.

Exercise 9 — IN for a set of countries

Return everyone whose country is one of: 'Pakistan', 'India', 'Bangladesh', 'Sri Lanka'.

Hint: country_of_origin IN (...).

Exercise 10 — NOT IN

Return everyone whose country is not one of 'USA', 'UK', 'Canada', 'Australia'.

Exercise 11 — BETWEEN for a date range

Return everyone born between 1985-01-01 and 1995-12-31 (inclusive).

Hint: dob BETWEEN '1985-01-01' AND '1995-12-31'.

Question: is BETWEEN inclusive on both ends in PostgreSQL?

Exercise 12 — LIKE starts-with pattern

Return everyone whose first_name starts with the letter A.

Hint: first_name LIKE 'A%'. The % matches zero or more characters.

Exercise 13 — LIKE contains-pattern

Return everyone whose last_name contains the letter a somewhere.

Hint: '%a%'.

Question: what if you also wanted matches on capital A? Read the next exercise.

Exercise 14 — ILIKE case-insensitive

Return everyone whose last_name contains 'a' in either case, using ILIKE.

Verify: compare your row count against exercise 13 — it should be larger (Smith, Ali, Khan, etc. all match differently).

Exercise 15 — LIKE with underscore

Return every person whose phone_number ends in '0'. Use a LIKE pattern.

Hint: '%0'.

Exercise 16 — IS NULL

Return first_name, last_name, and phone_number for every person whose phone_number is missing.

Question: why doesn't WHERE phone_number = NULL work?

Exercise 17 — IS NOT NULL

Return everyone who has an email on file.

Exercise 18 — Missing contact info

Return everyone who is missing both their email and their phone number.

Hint: two IS NULL conditions joined with AND.

Exercise 19 — First 10 rows with LIMIT

Return the first 10 people ordered alphabetically by last_name.

Hint: ORDER BY last_name LIMIT 10.

Exercise 20 — OFFSET for pagination

Return rows 11–20 (i.e. the second "page" of 10) when ordered alphabetically by last_name.

Hint: ORDER BY last_name LIMIT 10 OFFSET 10.

Exercise 21 — FETCH FIRST ... ROWS ONLY

Rewrite exercise 19 using the SQL-standard FETCH FIRST n ROWS ONLY syntax instead of LIMIT.

Hint:

SELECT ... FROM person
ORDER BY last_name
FETCH FIRST 10 ROWS ONLY;

Question: when would you prefer one syntax over the other?

Exercise 22 — Combine everything

Return the first 5 female people from countries starting with 'S', born after 1985-01-01, ordered by dob descending.

Hint: this is WHERE + AND + LIKE + ORDER BY ... DESC + LIMIT 5 all stacked.

Bonus challenges

Bonus 1 — Domain-specific email filter

Return everyone whose email ends with '@gmail.com'.

Bonus 2 — Tricky NULL

How many rows does SELECT COUNT(*) FROM person WHERE phone_number <> '020-555-0002'; return? Is it what you expected? (Hint: NULL <> anything is NULL, not TRUE.)

Bonus 3 — Two-character prefix

Return everyone whose first_name starts with 'Ma' using LIKE, and then rewrite the same query with SUBSTRING(first_name, 1, 2) = 'Ma'. Which one would run faster if first_name had an index?

Cleanup

DROP SCHEMA sqlf_filtering CASCADE;