Across the last few tutorials, we've shown you multiple ways to filter data. To review, we covered
Here's a comprehensive table of the different operators you can use in conjunction with to filter your data:
|Operator||Definition||Example in Query||Interpretation|
|=||Equals to||user_id is equal to 2|
|!=, <>||Not equals to||user_id is not equal to 5|
|<, >||Less than, more than||age is less than 5|
|<=, >=||Less than or equal to, more than or equal to||age is equal to or greater than 2|
|BETWEEN … AND …||Integer is within range of two values (inclusive)||pushups is between 20 and 40 (inclusive)|
|IN (…)||Value is in a list||first name is either Bob, Ann, or Joe|
|LIKE||Checks if a string matches a pattern||first name starts with "Jo"|
It's time to practice your filtering ability by analyzing a dataset of 1,000 customer records from a small-business in Australia.
Here's a sample of the input data you're given:
|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|
**Write a SQL query 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'. **
Your output would look something like this:
|402||Adeline Perrington||Female||22||1712||West Cooper||Tasmania|
|900||Audrie Frotton||Female||22||9455||East Emily||Tasmania|
Be warned – this exercise will need you to combine multiple SQL keywords in order to arrive at the solution!
In the practice exercise above where we filtered customer record data, we got the data in order of user_id, from lowest to highest.
But what if we wanted to make it in order of their age? And how could we handle ties?
That's where the next lesson comes in handy: ORDER BY Tutorial!
SQL ORDER BY ↕️