SQL Assessment: Find acting duets

SQL code copied to buffer

Task 98:

Find acting duos who have appeared together in more than five films.
Output the result as a table with columns: actor_id, first_name, last_name - identifier, first and last name of the first actor, actor_id, first_name, last_name - identifier, first and last name of the second actor and films_together - the number of films in which they starred together.
In each line, we first display the actor with the last name higher in alphabetical order. Sort the results in descending order of number of collaborations, then by the last name of the first actor.

Use MySQL syntax to write your answer. Descriptions of tables are provided in the right pane.

Write your request in the field below and click the "Check it!" button.

Get hint

Copy code Clear editor

Explore over 360 diverse tasks on our platform.

Sakila Database (MySQL)

Sakila is an example database designed by MySQL, specifically created for learning and demonstrating the capabilities of relational database management systems (RDBMS).

ER diagram of the Sakila database

The Sakila database contains 15 main tables describing various aspects of a DVD rental company.

Table List:

actor - actor table.

actor_idunique record identifier (PK).
first_nameactor's first name.
last_nameactor's last name.
last_updatedate and time of last update.

actor_id	first_name	last_name	last_update
1	John	Doe	2023-01-01 12:00:00

PRIMARY KEY, btree (actor_id)

address - customer and staff addresses.

address_idunique record identifier (PK).
addresspostal address.
address2additional address.
districtdistrict or region.
city_idcity identifier (FK).
postal_codepostal code.
phonephone number.
last_updatedate and time of last update.

address_id	address	address2	district	city_id	postal_code	phone	last_update
1	123 Main St	[null]	Downtown	1	12345	+1234567890	2023-01-01 12:00:00

PRIMARY KEY, btree (address_id)

category - film categories.

category_idunique record identifier (PK).
namecategory name.
last_updatedate and time of last update.

category_id	name	last_update
1	Action	2023-01-01 12:00:00

PRIMARY KEY, btree (category_id)

city - city table.

city_idunique record identifier (PK).
citycity name.
country_idcountry identifier (FK).
last_updatedate and time of last update.

city_id	city	country_id	last_update
1	Metropolis	1	2023-01-01 12:00:00

PRIMARY KEY, btree (city_id)

country - country table.

country_idunique record identifier (PK).
countrycountry name.
last_updatedate and time of last update.

country_id	country	last_update
1	United States	2023-01-01 12:00:00

PRIMARY KEY, btree (country_id)

customer - customer table.

customer_idunique record identifier (PK).
store_idstore identifier (FK).
first_namecustomer's first name.
last_namecustomer's last name.
emailcustomer's email address.
address_idaddress identifier (FK).
activecustomer activity indicator (0/1).
create_datedate and time the customer was added to the database.
last_updatedate and time of last update.

customer_id	store_id	first_name	last_name	email	address_id	active	create_date	last_update
1	1	John	Doe	john.doe@example.com	1	1	2023-01-01 12:00:00	2023-01-01 12:00:00

PRIMARY KEY, btree (customer_id)

film - list of films in Sakila database.

film_idunique record identifier (PK).
titlefilm title.
descriptionbrief description or plot of the film.
release_yearyear the film was released.
language_ididentifier of the film's language (FK).
original_language_ididentifier of the original language of the film in case it is dubbed into a new language.
rental_durationduration of rental period in days.
rental_ratecost of renting the film for the duration specified in the rental_duration column.
lengthlength of the film in minutes.
replacement_costamount of penalty for loss or damage of the disc.
ratingrating assigned to the film. Can be one of: G, PG, PG-13, R, or NC-17.
special_featureslist of special features included on the DVD. Can be zero or more of: Trailers, Commentaries, Deleted Scenes, Behind the Scenes.
last_updatedate and time of last update.

film_id	title	description	release_year	language_id	original_language_id	rental_duration	rental_rate	length	replacement_cost	rating	special_features	last_update
1	Film Title	A brief description of the film.	2000	1	2	5	4.99	120	19.99	PG-13	Trailers, Commentaries	2023-01-01 12:00:00

PRIMARY KEY, btree (film_id)

film_actor - actors to films relation.

actor_ididentifier for actor (FK).
film_ididentifier for film (FK).
last_updatedate and time of last update.

actor_id	film_id	last_update
1	1	2023-01-01 12:00:00

PRIMARY KEY, btree (actor_id, film_id)

film_category - films to categories relation.

film_ididentifier for each film (FK).
category_ididentifier for each category (FK).
last_updatedate and time of last update.

film_id	category_id	last_update
1	1	2023-01-01 12:00:00

PRIMARY KEY, btree (film_id, category_id)

inventory - items in Sakila database.

inventory_idunique record identifier (PK).
film_ididentifier of the film (FK).
store_ididentifier of the store where the inventory is located (FK).
last_updatedate and time of last update.

inventory_id	film_id	store_id	last_update
1	23	2	2023-01-01 12:00:00

PRIMARY KEY, btree (inventory_id)

language - films languages.

customer_idunique record identifier (PK).
namelanguage name.
last_updatedate and time of last update.

language_id	name	last_update
1	English	2023-01-01 12:00:00

PRIMARY KEY, btree (language_id)

payment - customers payments.

payment_idunique identifier of the record (PK).
customer_ididentifier of the customer (FK).
staff_ididentifier of the staff member who received the payment (FK).
rental_ididentifier of the rental record (FK).
amountpayment amount.
payment_datedate and time of the payment.
last_updatedate and time of the last update.

payment_id	customer_id	staff_id	rental_id	amount	payment_date	last_update
1	1	1	1	4.99	2023-01-01 12:13:14	2023-01-01 12:14:15

PRIMARY KEY, btree (payment_id)

rental - customers rentals.

rental_idunique identifier of the record (PK).
rental_daterental start date.
inventory_ididentifier of the disk (FK).
customer_ididentifier of the customer (FK).
return_datedate of returning the film.
staff_ididentifier of the staff member who issued the disk (FK).
last_updatedate and time of the last update.

rental_id	rental_date	inventory_id	customer_id	return_date	staff_id	last_update
1	2023-01-01 16:15:21	1	1	2023-01-10 09:12:36	1	2023-01-01 12:00:00

PRIMARY KEY, btree (rental_id)

staff - company staff.

staff_idunique identifier of the record (PK).
first_namefirst name of the staff member.
last_namelast name of the staff member.
address_ididentifier of the address (FK).
picturephoto of the staff member.
emailemail address of the staff member.
store_idforeign key referencing the store table (FK).
activeindicator of staff member's activity (0/1).
usernameusername for system login.
passwordpassword for login.
last_updatedate and time of the last update.

staff_id	first_name	last_name	address_id	picture	email	store_id	active	username	password	last_update
1	John	Doe	1	[null]	john.doe@example.com	1	1	johndoe	********	2023-01-01 12:00:00

PRIMARY KEY, btree (staff_id)

store - company stories.

store_idunique identifier of the record (PK).
manager_staff_ididentifier of the store manager (FK).
address_ididentifier of the address (FK).
last_updatedate and time of the last update.

store_id	manager_staff_id	address_id	last_update
1	1	1	2023-01-01 12:00:00

PRIMARY KEY, btree (store_id)