Combining filtering and selecting

Time for a little challenge. So far, your SQL vocabulary from this course includes COUNT(), DISTINCT, LIMIT, WHERE, OR, AND, BETWEEN, LIKE, NOT LIKE, and IN. In this exercise, you will try to use some of these together. Writing more complex queries will be standard for you as you become a qualified SQL programmer.

As this query will be a little more complicated than what you've seen so far, we've included a bit of code to get you started. You will be using DISTINCT here too because, surprise, there are two movies named 'Hamlet' in this dataset!

Follow the instructions to find out what 90's films we have in our dataset that would be suitable for English-speaking teens.

This exercise is part of the course

Intermediate SQL

Exercise instructions

Count the unique titles from the films database and use the alias provided.
Filter to include only movies with a release_year from 1990 to 1999, inclusive.
Add another filter narrowing your query down to English-language films.
Add a final filter to select only films with 'G', 'PG', 'PG-13' certifications.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Count the unique titles
SELECT ___ AS nineties_english_films_for_teens
FROM films
-- Filter to release_years to between 1990 and 1999
WHERE ___
-- Filter to English-language films
	___
-- Narrow it down to G, PG, and PG-13 certifications
	___;

Edit and Run Code

This exercise is part of the course

Intermediate SQL

BeginnerSkill Level

4.9+

Start Course for Free

In this first chapter, you’ll learn how to query a films database and select the data needed to answer questions about the movies and actors. You'll also understand how SQL code is executed and formatted.

Exercise 1: Querying a database Exercise 2: Learning to COUNT()Exercise 3: Practice with COUNT()Exercise 4: SELECT DISTINCT Exercise 5: Query execution Exercise 6: Order of execution Exercise 7: Debugging errors Exercise 8: SQL style Exercise 9: SQL best practices Exercise 10: Formatting Exercise 11: Non-standard fields

Learn about how you can filter numerical and textual data with SQL. Filtering is an important use for this language. You’ll learn how to use new keywords and operators to help you narrow down your query to get results that meet your desired criteria and gain a better understanding of NULL values and how to handle them.

Exercise 1: Filtering numbers Exercise 2: Filtering results Exercise 3: Using WHERE with numbers Exercise 4: Using WHERE with text Exercise 5: Multiple criteria Exercise 6: Using AND Exercise 7: Using OR Exercise 8: Using BETWEEN Exercise 9: Filtering text Exercise 10: LIKE and NOT LIKE Exercise 11: WHERE IN Exercise 12: Combining filtering and selecting

Current Exercise

Exercise 13: NULL values Exercise 14: What does NULL mean?Exercise 15: Practice with NULLs

SQL allows you to zoom in and out to better understand an entire dataset, its subsets, and its individual records. You'll learn to summarize data using aggregate functions and perform basic arithmetic calculations inside queries to gain insights into what makes a successful film.

Exercise 1: Summarizing data Exercise 2: Aggregate functions and data types Exercise 3: Practice with aggregate functions Exercise 4: Summarizing subsets Exercise 5: Combining aggregate functions with WHERE Exercise 6: Using ROUND()Exercise 7: ROUND() with a negative parameter Exercise 8: Aliasing and arithmetic Exercise 9: Using arithmetic Exercise 10: Aliasing with functions Exercise 11: Rounding results

This final chapter teaches you how to sort and group data. These skills will take your analyses to a new level by helping you uncover critical business insights and identify trends and performance. You'll get hands-on experience to determine which films performed the best and how movie durations and budgets changed over time.

Exercise 1: Sorting results Exercise 2: Sorting text Exercise 3: Sorting single fields Exercise 4: Sorting multiple fields Exercise 5: Grouping data Exercise 6: GROUP BY single fields Exercise 7: GROUP BY multiple fields Exercise 8: Answering business questions Exercise 9: Filtering grouped data Exercise 10: Filter with HAVING Exercise 11: HAVING and sorting Exercise 12: All together now Exercise 13: Congratulations!