The average length of films by category
From the previous exercise you've learned that the tables film and category have the necessary information to calculate the average movie length for every category. You've also learned that they share a common field film_id which can be used to join these tables. Now you will use this information to query a list of average length for each category.
This is a part of the course
“Applying SQL to Real-World Problems”
Exercise instructions
- Calculate the average
lengthand return this column asaverage_length. - Join the two tables
filmandcategory. - Ensure that the result is in ascending order by the average length of each category.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Calculate the average_length for each category
SELECT category,
___ AS average_length
FROM ___ AS f
-- Join the tables film & category
INNER JOIN ___ AS c
ON f.___ = c.___
GROUP BY ___
-- Sort the results in ascending order by length
___;This exercise is part of the course
Applying SQL to Real-World Problems
Find tables, store and manage new tables and views, and write maintainable SQL code to answer business questions.
How do you find the data you need in your database in order to answer real-world business questions? Here you will learn how to use system tables to explore your database. You will use these tables to create a new tool that contains a list of all tables and columns in your database. Finally, you will create an Entity Relationship Diagram (ERD) which will help you connect multiple tables.
Exercise 1: Find the right tableExercise 2: LIMITing your searchExercise 3: Which table to use?Exercise 4: What tables are in your database?Exercise 5: Determine the monthly incomeExercise 6: Join the correct tablesExercise 7: What columns are in your database?Exercise 8: A VIEW of all your columnsExercise 9: Testing out your new VIEWExercise 10: The average length of films by categoryExercise 11: Complex joinsExercise 12: Build the entity relationship diagramExercise 13: Which films are most frequently rented?What is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.