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.

---

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.

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

---

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 WITH en AS.
• Gebruik de query waarmee je details van films ophaalt.
  • Voeg hier een LEFT JOIN aantoe, met ON.
  • Koppel daarmee de data uit common table expression movie_roles aan de details van films.
    • Koppel op kolom 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. 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?

...

---

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?

...