Lab SQL Self and cross join
Jorge Castro DAPT NOV2021
In this lab, you will be using the Sakila database of movie rentals.
- Instructions:
- 1. Get all pairs of actors that worked together.
- 2. Get all pairs of customers that have rented the same film more than 3 times.
- 3. Get all possible pairs of actors and films
Instructions:
1. Get all pairs of actors that worked together.
Answer:
SELECT
f1.film_id,
f.title AS film_title,
f1.actor_id,
concat(a.first_name, ' ', a.last_name) AS actor_1,
f2.actor_id,
concat(a.first_name, ' ', a.last_name) AS actor_2
FROM
film_actor f1
INNER JOIN film_actor f2 ON f1.film_id = f2.film_id
AND f1.actor_id < f2.actor_id
INNER JOIN film f ON f.film_id = f1.film_id
INNER JOIN actor a ON f1.actor_id = a.actor_id
LIMIT
10;
f1.film_id,
f.title AS film_title,
f1.actor_id,
concat(a.first_name, ' ', a.last_name) AS actor_1,
f2.actor_id,
concat(a.first_name, ' ', a.last_name) AS actor_2
FROM
film_actor f1
INNER JOIN film_actor f2 ON f1.film_id = f2.film_id
AND f1.actor_id < f2.actor_id
INNER JOIN film f ON f.film_id = f1.film_id
INNER JOIN actor a ON f1.actor_id = a.actor_id
LIMIT
10;
Without the limit: 14915 row(s) returned
2. Get all pairs of customers that have rented the same film more than 3 times.
Answer:
3
ORDER BY
5 DESC
LIMIT
10;">SELECT
c1.customer_id,
concat(c1.first_name, ' ', c1.last_name) AS customer_name1,
c2.customer_id,
concat(c2.first_name, ' ', c2.last_name) AS customer_name2,
count(r1.rental_id) AS number_of_rents_over_3_times
FROM
customer c1
INNER JOIN rental r1 ON r1.customer_id = c1.customer_id
INNER JOIN inventory i1 ON i1.inventory_id = r1.inventory_id
INNER JOIN film f1 ON i1.film_id = f1.film_id
INNER JOIN inventory i2 ON f1.film_id = i2.film_id
INNER JOIN rental r2 ON i2.inventory_id = r2.inventory_id
INNER JOIN customer c2 ON r2.customer_id = c2.customer_id
WHERE
c1.customer_id != c2.customer_id
GROUP BY
1,
3
HAVING
count(*) > 3
ORDER BY
5 DESC
LIMIT
10;
c1.customer_id,
concat(c1.first_name, ' ', c1.last_name) AS customer_name1,
c2.customer_id,
concat(c2.first_name, ' ', c2.last_name) AS customer_name2,
count(r1.rental_id) AS number_of_rents_over_3_times
FROM
customer c1
INNER JOIN rental r1 ON r1.customer_id = c1.customer_id
INNER JOIN inventory i1 ON i1.inventory_id = r1.inventory_id
INNER JOIN film f1 ON i1.film_id = f1.film_id
INNER JOIN inventory i2 ON f1.film_id = i2.film_id
INNER JOIN rental r2 ON i2.inventory_id = r2.inventory_id
INNER JOIN customer c2 ON r2.customer_id = c2.customer_id
WHERE
c1.customer_id != c2.customer_id
GROUP BY
1,
3
HAVING
count(*) > 3
ORDER BY
5 DESC
LIMIT
10;
Without limit 10: 4304 row(s) returned
3. Get all possible pairs of actors and films
Answer:
f2.actor_id
AND f1.film_id = f2.film_id
INNER JOIN actor a1 ON f1.actor_id = a1.actor_id
INNER JOIN actor a2 ON f2.actor_id = a2.actor_id
INNER JOIN film f ON f1.film_id = f.film_id
ORDER BY
1,
2,
3
LIMIT
5;">SELECT
f.title AS film_title,
CONCAT(a1.first_name, ' ', a1.last_name) AS actor_1,
CONCAT(a2.first_name, ' ', a2.last_name) AS actor_2
FROM
film_actor f1
INNER JOIN film_actor f2 ON f1.actor_id > f2.actor_id
AND f1.film_id = f2.film_id
INNER JOIN actor a1 ON f1.actor_id = a1.actor_id
INNER JOIN actor a2 ON f2.actor_id = a2.actor_id
INNER JOIN film f ON f1.film_id = f.film_id
ORDER BY
1,
2,
3
LIMIT
5;
f.title AS film_title,
CONCAT(a1.first_name, ' ', a1.last_name) AS actor_1,
CONCAT(a2.first_name, ' ', a2.last_name) AS actor_2
FROM
film_actor f1
INNER JOIN film_actor f2 ON f1.actor_id > f2.actor_id
AND f1.film_id = f2.film_id
INNER JOIN actor a1 ON f1.actor_id = a1.actor_id
INNER JOIN actor a2 ON f2.actor_id = a2.actor_id
INNER JOIN film f ON f1.film_id = f.film_id
ORDER BY
1,
2,
3
LIMIT
5;
Without the limit: 14915 row(s) returned
Top Section
Bottom section