Extra challenges (optional)

Overview

Teaching: 0 min
Exercises: 35 min

Questions

• Are there extra challenges to practice translating plain English queries to SQL queries?

Objectives

• Extra challenges to practice creating SQL queries.

Extra challenges (optional)

SQL queries help us ask specific questions which we want to answer about our data. The real skill with SQL is to know how to translate our questions into a sensible SQL queries (and subsequently visualise and interpret our results).

Have a look at the following questions; these questions are written in plain English. Can you translate them to SQL queries and give a suitable answer?

Also, if you would like to learn more SQL concepts and try additional challenges, see the Software Carpentry Databases and SQL lesson.

Challenge 1

How many series are there for each genre?

Solution 1

SELECT genre, COUNT( * )
FROM genres
JOIN filmsAndSeries
USING (id)
WHERE type = "SHOW"
GROUP BY genre;

Challenge 2

How many shows ran for only one season? How many ran for 2 seasons? How many 3? etc?

Solution 2

SELECT seasons, COUNT( * )
FROM filmsAndSeries
WHERE type = "SHOW"
GROUP BY seasons

Challenge 3

How many movies or shows are there from each country? Can you make an alias for the number of movies? Can you order the results by number of movies?

Solution 3

SELECT countries.country, COUNT( * ) AS n_movies
FROM productionCountries
JOIN countries
ON countries.code = productionCountries.country
GROUP BY productionCountries.country
ORDER BY n_movies DESC;

Challenge 4

How many titles are there for each age_certification type, and what is the average imdb_score for that age_certification type?

Solution 4

SELECT age_certification, AVG( imdb_score ), COUNT( * )
FROM filmsAndSeries
GROUP BY Licence;

Key Points

• It takes time and practice to learn how to translate plain English queries into SQL queries.