Ga naar inhoud

Header

Subqueries introductie - Opdrachten

1. Films met een score hoger dan gemiddeld

In onze database hebben we gegevens van films, tabel movies:

Header

Ieder film heeft een score, in kolom rank.

We zijn in deze opdracht benieuwd naar de films met een hogere score dan gemiddeld.

  • Hier gaan we stap voor stap mee aan de slag.
  • Daarbij gaan we een subquery gebruiken.



1.1. Query om details van films op te halen

Maak onderstaande query af om details van films op te halen.

  • Haal data op uit tabel movies.
  • Selecteer kolommen id, name, en rank.

Je moet de volgende output zien:

id name rank
10920 Aliens 8.2
17173 Animal House 7.5
18979 Apollo 13 7.5
30959 Batman Begins (NULL)
46169 Braveheart 8.3
... ... ... 
Columns: 3
Rows: 36

SELECT
  id,
  name,
  rank
FROM
  movies;



1.2. Query om gemiddelde score te berekenen

Maak onderstaande query af om de gemiddelde score van films te berekenen.

  • Haal data op uit tabel movies.
  • Selecteer en bereken met functie AVG() het gemiddelde van de scores, kolom rank.

Je moet één getal terugkrijgen.

SELECT
  AVG(rank)
FROM
  movies;

7.791176470588234



1.3. Query om films te filteren afhankelijk van de score

Maak onderstaande query af waarbij je een filter toepast. Zodang dat alleen films met een score groter dan een bepaalde waarde getoond worden.

  • Hergebruik je code uit vraag 1.1.
  • Voeg een WHERE statement toe.
    • Filter hiermee op kolom rank, de score.
      • Selecteer waarden groter dan een score van 7.7.

Controleer het resultaat.

SELECT
  id,
  name,
  rank
FROM
  movies
WHERE
  rank > 7.7;
id name rank
10920 Aliens 8.2
46169 Braveheart 8.3
109093 Fargo 8.2
112290 Fight Club 8.5
124110 Garden State 8.3
... ... ... 
Columns: 3
Rows: 20



1.4. Toevoegen van een subquery

In de vorige vraag hebben we de gemiddelde score in het filter hardcoded toegevoegd.

In de vraag daarvoor hebben we een query geschreven die de gemiddelde score berekent.

Hergebruik je eerdere code en pas een subquery toe. Zodanig dat je de gemiddelde score berekent in plaats van hardcoded toevoegt.

  • Hergebruik je vode uit de vorige vraag.
  • Vergang getal 7.7 in het WHERE statment door een subquery.
    • De subquery moet je eerdere query zijn.
      • Waarmee je de gemiddelde score berekent.

Controleer het resultaat.

SELECT
  id,
  name,
  rank
FROM
  movies
WHERE
  rank > (
    SELECT
      AVG(rank)
    FROM
      movies
  );
id name rank
10920 Aliens 8.2
46169 Braveheart 8.3
109093 Fargo 8.2
112290 Fight Club 8.5
124110 Garden State 8.3
... ... ... 
Columns: 3
Rows: 20



2. (Extra) Films met een score hoger dan gemiddeld

In de vorige opdracht heb je informatie van films opgehaald gebaseerd op de score.

Nu moet je iets vergelijkbaars gaan doen.

Schrijf een query waarmee je de films selecteert waarvan de naam langer is dan de gemiddelde lengte.

  • Maak gebruik van een subquery.
  • Toon de volgende kolommen in je uitkomst:
    • id
    • name
    • Lengte van name
  • Sorteer op de kolom met de lengte van name.
  • Maak onder andere gebruik van: SELECT, FROM, WHERE, AVG(), LENGTH(), ORDER BY.
SELECT
  id,
  name,
  LENGTH(name) AS name_length
FROM
  movies
WHERE
  LENGTH(name) > (
    SELECT
      AVG(LENGTH(name))
    FROM
      movies
  )
ORDER BY
  LENGTH(name);
id name name_length
30959 Batman Begins 13
130128 Godfather, The 14
314965 Stir of Echoes 14
276217 Reservoir Dogs 14
111813 Few Good Men, A 15
238072 Ocean”s Eleven 16
176712 Kill Bill: Vol. 2 17
176711 Kill Bill: Vol. 1 17
194874 Lost in Translation 19
192017 Little Mermaid, The 19
256630 Pirates of the Caribbean 24
297838 Shawshank Redemption, The 25
237431 O Brother, Where Art Thou? 26
257264 Planes, Trains & Automobiles 28 
Columns: 3
Rows: 14



3. (Extra) Acteurs op basis van rollen

Je gaat een complexe query schrijven.

We maken hierbij gebruik van 2 tabellen:

  • actors: details van acteurs.
  • roles: details van filmrollen.

Je gaat het volgende doen:

  • Maak een query waarmee je de meest voorkomende eerste 2 letters van een naam van een filmrol bepaalt.
    • Voorbeeld: rolnaam Tyrone eerste 2 letters: Ty.
    • Gebruik daarbij: SELECT, LEFT(), COUNT(), FROM GROUP BY, ORDER BY ... DESC, LIMIT 1.
  • Maak een query waarmee je de vorige query als subquery gebruikt.
    • Selecteer hiermee alleen de bepaalde 2 letters.
      • Voorbeeld: Ty.
    • Gebruik daarbij: SELECT, FROM en een subquery.
  • Maak een query waarmee je de vorige queries als subquery gebruikt.
    • Zodanig dat je details van acteurs selecteert. Daar waarvan de eerste 2 letters van hun voornaam gelijk zijn aan de bepaalde 2 letters.
    • Gebruik daarbij: SELECT, FROM, WHERE, LEFT, en een subquery.

Tip: Schrijf je queries stap voor stap. Zodat je de resultaten tussentijds kunt controleren.

SELECT
  id,
  first_name,
  last_name
FROM
  actors
WHERE
  LEFT(first_name, 2) = (
    SELECT
      most_used_character.left
    FROM
      (
        SELECT
          LEFT(role, 2),
          COUNT(role)
        FROM
          roles
        GROUP BY
          LEFT(role, 2)
        ORDER BY
          COUNT(role) DESC
        LIMIT
          1
      ) as most_used_character
  );
id first_name last_name
241542 Hiroshi (I) Kawashima
319868 Hikaru Midorikawa
672860 Hiroko Kawasaki 
Columns: 3
Rows: 3