Header

Common table expressions - Opdrachten

1. Aantal rollen in films

We gaan stap voor stap aan de slag met data uit de IMDb database.

Daarbij moet je het volgende gaan verkrijgen:

Een overzicht van alle films.
Met daaraan toegvoegd het aantal rollen van iedere film.

We maken gebruik van tabellen movies en roles.

Header

1.1. Details van films

We starten met details van films.

Hiervoor kun je onderstaande query gebruiken.

Voer deze query uit en bekijk het resultaat.

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

Je hebt hiermee details van alle films verkregen.

1.2. Aantal rollen per film

Tabel roles bevat details van filmrollen.

Schrijf een query die het volgende doet:

Per film het aantal rollen tellen.

Doe daarbij het volgende:

Haal data op uit tabel roles met FROM.
Groepeer op kolom movie_id met GROUP BY.
Selecteer de volgende kolommen met SELECT:
- movie_id
- Tel het aantal rollen met COUNT(*)
  - Noem deze nieuwe kolom roles_count met AS.

SELECT
  movie_id,
  COUNT(*) AS roles_count
FROM
  roles
GROUP BY
  movie_id;

movie_id	roles_count
238695	33
30959	62
17173	43
...	...

Columns: 2
Rows: 36

1.3. Gebruik van een common table expression

Je gaat nu de 2 eerdere queries combineren.

Hierbij ga je gebruik maken van het volgende:

LEFT JOIN om de data samen te voegen.
Een common table expression.

Ga als volgt te werk:

Hergebruik je eerdere code.
Maak een common table expression aan met de naam movie_roles.
- Hierin plaats je de query waarmee je het aantal rollen per film ophaalt.
Gebruik de query waarmee je details van films ophaalt.
- Voeg hier een LEFT JOIN aantoe.
- Koppel daarmee de data uit common table expression movie_roles aan de details van films.
  - Koppel op kolom id / movie_id.

WITH movie_roles AS (
  SELECT
    movie_id,
    COUNT(*) AS roles_count
  FROM
    roles
  GROUP BY
    movie_id
)
SELECT
  m.*,
  movie_roles.roles_count
FROM
  movies AS m
  LEFT JOIN movie_roles ON m.id = movie_roles.movie_id;

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

Columns: 5
Rows: 36

1.4. Verbeter het resultaat

De uitkomst van de vorige query was nog niet heel goed leesbaar.

Dit doordat:

Alle kolommen uit tabel movies werden getoond.
Er niet was gesorteerd op het aantal rollen.

Hier ga je verandering in aanbrengen.

Pas de query als volgt aan:

Selecteer uit tabel movies alleen kolommen id en name.
Sorteer op kolom roles_count met ORDER BY en DESC.

Welke film heeft de meeste rollen?

WITH movie_roles AS (
  SELECT
    movie_id,
    COUNT(*) AS roles_count
  FROM
    roles
  GROUP BY
    movie_id
)
SELECT
  m.id,
  m.name,
  movie_roles.roles_count
FROM
  movies AS m
  LEFT JOIN movie_roles ON m.id = movie_roles.movie_id
ORDER BY
  roles_count DESC;

id	name	roles_count
167324	JFK	230
333856	Titanic	130
313459	Star Wars	104
...	...	...

Columns: 3
Rows: 36

2. (Extra) Aantal genres van films

Je gaat nu zelf aan de slag met een common table expression.

Deze opdracht lijkt qua opzet erg op de vorige.

Daarbij moet je het volgende gaan verkrijgen:

Een overzicht van alle films.
Met daaraan toegvoegd het aantal genres van iedere film.
Gebruik data uit tabellen movies en movies_genres.
Gebruik een common table expression.

Welke film heeft de meeste genres?

WITH movie_genres AS (
  SELECT
    movie_id,
    COUNT(*) AS genres_count
  FROM
    movies_genres
  GROUP BY
    movie_id
)
SELECT
  m.id,
  m.name,
  movie_genres.genres_count
FROM
  movies AS m
  LEFT JOIN movie_genres ON m.id = movie_genres.movie_id
ORDER BY
  genres_count DESC;

id	name	genres_count
192017	Little Mermaid, The	6
300229	Shrek	6
350424	Vanilla Sky	5
...	...	...

Columns: 3
Rows: 36