Filtering with comparison operators
1. Filtering with comparison operators
Welcome back. So far, we've learned to sort, group, and summarize.2. Workflow
These are key techniques for exploring and analyzing data before communicating insights. Filtering sharpens this process by ensuring we focus on relevant data.3. Filtering records
Let's look at our bookshelf again. We're interested in reading a sci-fi book; we could sort or group the data, but we'd still include all the other books. With filtering, we can extract only the sci-fi books and look through those. This filtering process narrows the results so we only see the needed records. With large datasets, filtering also makes our queries run faster since fewer records need to be processed.4. Filtering with WHERE
In SQL, the WHERE clause filters across all data types. To see only films released in the year 2000, we can prompt: "Show films released in the year 2000". The result is a query with a filter for the specified year, where the equals operator checks for exact matches.5. Excluding with WHERE
We can also exclude data. "List all films except those released in 2000" uses a "not equal to" operator to filter out films released that year. This operator can appear in two forms; less-than greater-than, or exclamation mark equals. Both work the same way.6. Comparison operators
Beyond exact matches, we can filter for values that are greater than, less than, greater than or equal to, and less than or equal to.7. Comparison operators
We can use these same terms within our prompts or try variations like "Show me the title and release year of films released before 2000" or "after 2000". We've specified the title and release year here to verify that the filtering worked by reviewing the years in the result.8. Prompting tips
Other prompt options for filtering numbers can include "on or before", "at least", "or more", "or earlier", and "or less" to generate a greater than or equal to, or less than or equal to filter. We can experiment and iterate until we find the prompt that works best.9. Filtering text
Comparison operators can also work with text by comparing values alphabetically, but conventionally, we use equals and not-equals for text filtering. For example, "Show me the title and country of films from the USA" results in a query that uses single quotes around the text criteria.10. Filtering text: precision needed
Precision is key with this approach, as asking for "US films" will result in an incorrect filter, since "US" isn't in our data. We'll learn more about matching text patterns later.11. Combining techniques
Filtering becomes especially powerful when combined with the other techniques we've learned. Let's try out some combinations before we head to the exercises. What if we want the average duration for US films? We can combine filtering with aggregate functions using a prompt like, "What's the average duration for films from the USA?". This query filters first, then averages the durations.12. Combining techniques
Let's kick it up a notch by layering techniques to see which languages have had the longest films recently. For example, we could prompt: "Show average duration in descending order grouped by language for films after 2000". This single query filters, groups, summarizes, and sorts. The result shows that Swedish films come out on top.13. Time to practice!
Time for some practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.