Back to questions
SQL Filtering Practice Exercise #1 [SQL Interview Question]
You have a table of 1000 customer records from a small-business based in Australia.
Find all customers who are between the ages of 18 and 22 (inclusive), live in either Victoria, Tasmania, Queensland, their gender isn't "n/a", and their name starts with either 'A' or 'B'.
Schema:
| Column Name | Type |
|---|---|
| customer_id | integer |
| customer_name | string |
| gender | string |
| age | integer |
| zip_code | integer |
| city | string |
| state | string |
Sample Input:
| customer_id | customer_name | gender | age | zip_code | city | state |
|---|---|---|---|---|---|---|
| 1 | Ignace Whillock | Male | 30 | 5464 | Johnstonhaven | Northern Territory |
| 2 | Gray Eskrick | Female | 69 | 8223 | New Zacharyfort | South Australia |
| 3 | Ellswerth Laurent | Male | 59 | 5661 | Aliburgh | Australian Capital Territory |
| ... | .... | ... | .... | ... | ... | ... |
Example Output
| customer_id | customer_name | gender | age | zip_code | city | state |
|---|---|---|---|---|---|---|
| 123 | Aidan Goseling | Female | 22 | 1149 | Jordanborough | Tasmania |
| 402 | Adeline Perrington | Female | 22 | 1712 | West Cooper | Tasmania |
| 692 | Bertrando Melonby | Male | 20 | 4058 | Laraview | Tasmania |
| 900 | Audrie Frotton | Female | 22 | 9455 | East Emily | Tasmania |
Solution
For larger queries, it's very easy to accidentally make a mistake, and not understand where you went wrong. As such, we'll break down the problem statement into smaller pieces, and code up each part incrementally. Each time we make our query bigger, we'll run the code to validate that it worked!
**All customers who are between the ages of 18 and 22 (inclusive): **
**All customers who are between the ages of 18 and 22 (inclusive) AND who live in either Victoria, Tasmania, Queensland: **
**All customers who are between the ages of 18 and 22 (inclusive) AND who live in either Victoria, Tasmania, Queensland AND their gender isn't "n/a": **
**All customers whose name starts with either 'A' or 'B': **
Combining all this together, to find all customers who are between the ages of 18 and 22 (inclusive), live in either Victoria, Tasmania, Queensland, their gender isn't "n/a", and their name starts with either 'A' or 'B':
Be careful with the parens on the last statement – it's very easy to accidentally forget that, and just end up with a naked OR customer_name LIKE 'B%' so you just get a bunch of customer records whose name starts with B but don't match any of the other conditions!
PostgreSQL 14