Subqueries introduction - Assignments

1. Movies with a score higher than average

In our database, we have data of movies, table movies:

Each movie has a score, in column rank.

In this assignment, we are interested in the movies with a higher score than average.

• We will tackle this step by step.
• We will use a subquery.

---

1.1. Query to retrieve movie details

Complete the query below to retrieve movie details.

• Retrieve data from table movies.
• Select columns id, name, and rank.

You should see the following output:

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 to calculate average score

Complete the query below to calculate the average score of movies.

• Retrieve data from table movies.
• Select and calculate the average of the scores, column rank, using the AVG() function.

You should get one number back.

SELECT
  AVG(rank)
FROM
  movies;

7.791176470588234

---

1.3. Query to filter movies based on score

Complete the query below by applying a filter. So that only movies with a score greater than a certain value are shown.

• Reuse your code from question 1.1.
• Add a WHERE statement.
  • Filter on column rank, the score.
    • Select values greater than a score of 7.7.

Check the result.

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. Adding a subquery

In the previous question, we hardcoded the average score in the filter.

In the question before that, we wrote a query that calculates the average score.

Reuse your previous code and apply a subquery. So that you calculate the average score instead of hardcoding it.

• Reuse your code from the previous question.
• Replace number 7.7 in the WHERE statement with a subquery.
  • The subquery should be your previous query.
    • Which calculates the average score.

Check the result.

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) Movies with a score higher than average

In the previous assignment, you retrieved information about movies based on the score.

Now you need to do something similar.

Write a query to select movies whose name is longer than the average length.

• Use a subquery.
• Show the following columns in your result:
  • id
  • name
  • Length of name
• Sort by the column with the length of name.
• Use, among others: 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) Actors based on roles

You are going to write a complex query.

We will use 2 tables:

• actors: details of actors.
• roles: details of movie roles.

You will do the following:

• Write a query to determine the most common first 2 letters of a movie role name.
  • Example: role name Tyrone first 2 letters: Ty.
  • Use: SELECT, LEFT(), COUNT(), FROM GROUP BY, ORDER BY ... DESC, LIMIT 1.
• Write a query using the previous query as a subquery.
  • Select only the determined 2 letters.
    • Example: Ty.
  • Use: SELECT, FROM, and a subquery.
• Write a query using the previous queries as a subquery.
  • So that you select details of actors whose first name starts with the determined 2 letters.
  • Use: SELECT, FROM, WHERE, LEFT, and a subquery.

Tip: Write your queries step by step. So you can check the results in between.

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