Merging Data with LEFT JOIN - Exercises

1. The roles of actors

We have data of actors (table actors). And data of roles (table roles).

We now want to know which actor fulfills which roles.

For this, we will use LEFT JOIN.

Complete the query below to merge the tables.

• Left table: actors.
• Right table: roles.
• Column id from table actors matches with column actor_id from table roles.
• Use LEFT JOIN and ON.
• Select all columns.

...
  ...
FROM
  actors
  ... ... ON ... = ...;

---

2. Use aliases

In the previous exercise, we wrote out the table names in full.

We will now use aliases with AS.

Reuse your code from the previous exercise and apply aliases.

• Name table actors: a.
• Name table roles: r.

...
  ...
FROM
  actors ... ...
  ... ... ON ... = ...;

---

3. Make a specific selection

So far, we have selected all columns.

We will now make a specific selection.

Reuse your code from the previous exercise and make a specific selection.

• Select all columns from table actors.
• Select only column role from table roles.

...
  ...,
  ...
FROM
  actors ... ...
  ... ... ON ... = ...;

---

4. (Extra) Grouping

We now have a table with all the roles of actors.

By grouping, we will determine how many roles an actor has played.

• Table actors already contains a column film_count.
• But maybe an actor plays multiple roles in one film.

Reuse your previous code and write a query.

• Use GROUP BY.
• Group by column id from table actors.
• Count the number of roles with function COUNT().
• Select the following columns:
  • first_name.
  • last_name.
  • COUNT(...).

...

---

5. (Extra) Filtering and sorting

We now have a table with the number of roles of actors.

By filtering and sorting, we will make the result more useful.

Reuse your previous code and write a query.

Such that: * Filter so that only actors with more than 1 role appear. * Use HAVING. * Sort descending by the number of roles. * Use ORDER BY.

...