Subqueries with EXISTS - Assignments

1. Actors with a specific role

In the IMDb database, we have data on actors and roles:

We are interested in the actors who have played a specific role.

• Roles that start with the letter Q.

Write a query that does this:

• Select all columns from the actors table.
• Only the actors who have played a role that starts with the letter Q.

Make use of the following:

• SELECT, FROM, and WHERE.
• A subquery with EXISTS.
  • Select all columns from the roles table.
  • Where there is a match:
    • Between the matching columns from the actors and roles tables.
    • And values in the role column start with the letter Q.
      • Use LIKE.

SELECT
  *
FROM
  actors
WHERE
  EXISTS (
    SELECT
      *
    FROM
      roles
    WHERE
      actors.id = roles.actor_id
      AND role LIKE 'Q%'
  );

---

2.(Extra) Actors with more than 2 roles

The query below retrieves details of actors who have played more than 2 roles:

SELECT
  a.first_name,
  a.last_name
FROM
  actors AS a
  LEFT JOIN roles AS r ON a.id = r.actor_id
GROUP BY
  a.id
HAVING
  COUNT(r.role) > 2;

first_name	last_name
Bill	Paxton
Kevin	Bacon
Steve	Buscemi
Michael (I)	Madsen
Uma	Thurman
Brad	Pitt
Stevo	Polyi

Columns: 2
Rows: 7

Here, a LEFT JOIN is used.

Write a query that gives the same result, but using a subquery with EXISTS.

SELECT
  first_name, last_name
FROM
  actors
WHERE
  EXISTS (
    SELECT
      actor_id,
      count(actor_id)
    FROM
      roles
    WHERE
      actors.id = roles.actor_id
    GROUP BY
      actor_id
    HAVING
      count(actor_id) > 2
  );