Filtering with comparison operators

Intermediate SQL with AI

Jasmin Ludolf

Senior Data Science Content Developer

Workflow

Workflow to explore, analyze, and communicate

Sort, group, and summarize to explore and analyze
Filter to focus on relevant data

Filtering records

Narrow the results to see only needed records
Makes queries run faster: fewer records processed

Messy bookshelf

Filtering with WHERE

Prompt: Show films released in the year 2000

SELECT *
FROM films
WHERE release_year = 2000;

|id  |title         |release_year|country|duration|language|certification|gross   |budget  |
|----|--------------|------------|-------|--------|--------|-------------|--------|--------|
|1338|102 Dalmatians|2000        |USA    |100     |English |G            |66941559|85000000|
|1339|28 Days       |2000        |USA    |103     |English |PG-13        |37035515|43000000|
|1340|3 Strikes     |2000        |USA    |82      |English |R            |9821335 |6000000 |
|1341|Aberdeen      |2000        |UK     |106     |English |             |64148   |6500000 |
...

Excluding with WHERE

Prompt: List all films except those released in 2000

SELECT *
FROM films
WHERE release_year <> 2000;

|id  |title         |release_year|country|duration|language|certification|gross   |budget  |
|----|--------------|------------|-------|--------|--------|-------------|--------|--------|
|1   |Intolerance...|1916        |USA    |123     |        |Not Rated    |        |385907  |
|2   |Over the Hi...|1920        |USA    |110     |        |             |3000000 |100000  |
|3   |The Big Parade|1925        |USA    |151     |English |R            |        |245000  |
|4   |Metropolis    |1927        |Germany|145     |German  |Not Rated    |64148   |6500000 |
...

Not equal to: <>, !=

Comparison operators

Exact matches:

=
<> or !=

Beyond:

> greater than
< less than
>= greater than or equal to
<= less than or equal to

Comparison operators

Prompt: Show me the title and release year of films released before 2000

SELECT title, release_year
FROM films
WHERE release_year < 2000;

|title                 |release_year|
|----------------------|------------|
|Over the Hill to th...|1920        |
...
|The Muppet Movie      |1979        |
...
|Notting Hill          |1999        |
...

Prompt: Show me the title and release year of films released after 2000

SELECT title, release_year
FROM films
WHERE release_year > 2000;

|title                 |release_year|
|----------------------|------------|
|15 Minutes            |2001        |
|3000 Miles to Grace...|2001        |
|A Beautiful Mind      |2001        |
|A Knight's Tale       |2001        |
...

Prompting tips

"on or before..." <=
"at least..." >=
"... or more" >=
"...or earlier" <=
"... or less" <=

Experiment and iterate to find the best prompt

3D illustration of a speech bubbles, one with AI and one with ellipsis

Filtering text

Prompt: Show me the title and country of films from the USA

SELECT title, country
FROM films
WHERE country = 'USA';

|title                                           |country|
|------------------------------------------------|-------|
|Intolerance: Love's Struggle Throughout the Ages|USA    |
|Over the Hill to the Poorhouse                  |USA    |
|The Big Parade                                  |USA    |
|The Broadway Melody                             |USA    |
...

Filtering text: precision needed

Prompt: Show me the title and country of films from the US

SELECT title, country
FROM films
WHERE country = 'US';

|title|country|
|-----|-------|
|     |       |

Combining techniques

Prompt: What's the average duration for films from the USA?

SELECT AVG(duration) AS average_duration
FROM films
WHERE country = 'USA';

|average_duration|
|----------------|
|107.357104700...|

Combining techniques

Prompt: Show average duration in descending order grouped by language for films after 2000

SELECT language, AVG(duration) AS average_duration
FROM films
WHERE release_year > 2000
GROUP BY language
ORDER BY average_duration DESC;

|language|average_duration|
|--------|----------------|
|Kannada |                |
|Urdu    |                |
|Swedish |175.666666666...|
|Thai    |173.666666666...|
...

Time to practice!

Intermediate SQL with AI