Back to questions

SQL Filtering Practice Exercise #1 SQL Interview Question

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!

Difficulty

Easy

Input

(PostgreSQL)

Output