Common table expressions - Assignments

1. Number of roles in films

We will work step by step with data from the IMDb database.

You need to obtain the following:

• An overview of all films.
• With the number of roles for each film added.

We use the tables movies and roles.

---

1.1. Details of films

We start with details of films.

For this, you can use the query below.

Execute this query and view the result.

SELECT
  m.*
FROM
  movies AS m;

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

You have obtained details of all films with this.

---

1.2. Number of roles per film

Table roles contains details of film roles.

Write a query that does the following:

• Count the number of roles per film.

Do the following:

• Retrieve data from table roles with FROM.
• Group by column movie_id with GROUP BY.
• Select the following columns with SELECT:
  • movie_id
  • Count the number of roles with COUNT(*)
    • Name this new column roles_count with AS.

...
  ...,
  ... AS roles_count
FROM
  roles
...
  ...;

---

1.3. Use of a common table expression

You will now combine the 2 previous queries.

You will use the following:

• LEFT JOIN to merge the data.
• A common table expression.

Proceed as follows:

• Reuse your previous code.
• Create a common table expression named movie_roles.
  • Place the query that retrieves the number of roles per film in it.
  • Use WITH and AS.
• Use the query that retrieves details of films.
  • Add a LEFT JOIN to it, with ON.
  • Link the data from common table expression movie_roles to the details of films.
    • Link on column id / movie_id.

... movie_roles .. (
  ...
)
SELECT
  m.*,
  movie_roles.roles_count
FROM
  movies AS m
  ... movie_roles ... m.id ... movie_roles.movie_id;

---

1.4. Improve the result

The outcome of the previous query was not very readable.

This is because:

• All columns from table movies were shown.
• It was not sorted by the number of roles.

You will change this.

Adjust the query as follows:

• Select only columns id and name from table movies.
• Sort by column roles_count with ORDER BY and DESC.

Which film has the most roles?

...

---

2. (Extra) Number of genres of films

You will now work with a common table expression yourself.

This assignment is very similar in setup to the previous one.

You need to obtain the following:

• An overview of all films.
• With the number of genres for each film added.
• Use data from tables movies and movies_genres.
• Use a common table expression.

Which film has the most genres?

...