Задание 30:

Анализ стоимости проката фильма по категории

Найдите среднюю стоимость проката фильма и среднюю фактическую сумму аренды фильма для каждой категории
Результат выведите в три колонки category, avg_rental_rate и  avg_payment_amount.
Отсортируйте результат по убыванию разницы между фактической и номинальной стоимостью.

Для написания ответа используйте синтаксис MySQL. Описания таблиц приведены в правой панели.

Напишите ваш запрос в поле ниже и нажмите кнопку "Проверить!"

Подсказка

Копировать код Очистить

База данных Sakila: описание таблиц и структуры

Sakila - это пример реляционной базы данных, разработанный компанией MySQL для обучения и демонстрации возможностей SQL и систем управления базами данных (СУБД).

На этой странице собрана структура таблиц Sakila, основные поля и ключи, которые используются в учебных SQL-запросах.

База данных Sakila содержит 15 основных таблиц, описывающих различные аспекты компании по прокату DVD-дисков.

ER-диаграмма базы данных Sakila со связями между таблицами ER диаграмма базы данных Sakila

Список таблиц

actor - таблица актеров

actor_id уникальный идентификатор записи (ПК).
first_name имя актера.
last_name фамилия актера.
last_update дата и время последнего изменения.

Пример структуры таблицы actor
actor_id	first_name	last_name	last_update
1	John	Doe	2023-01-01 12:00:00

PRIMARY KEY, btree (actor_id)

address - адреса клиентов и сотрудников

address_id уникальный идентификатор записи (ПК).
address почтовый адрес.
address2 дополнительный адрес.
district район или регион.
city_id идентификатор городов (ВК).
postal_code почтовый индекс.
phone номер телефона.
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)

FOREIGN KEY (city_id) REFERENCES city(city_id)

category - категории фильмов

category_id уникальный идентификатор записи (ПК).
name название категории.
last_update дата и время последнего изменения.

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

PRIMARY KEY, btree (category_id)

city - таблица городов

city_id уникальный идентификатор записи (ПК).
city название города.
country_id идентификатор страны (ВК).
last_update дата и время последнего изменения.

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

PRIMARY KEY, btree (city_id)

FOREIGN KEY (country_id) REFERENCES country(country_id)

country - таблица стран

country_id уникальный идентификатор записи (ПК).
country название страны.
last_update дата и время последнего изменения.

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

PRIMARY KEY, btree (country_id)

customer - таблица клиентов

customer_id уникальный идентификатор записи (ПК).
store_id идентификатор магазина (ВК).
first_name имя клиента.
last_name фамилия клиента.
email адрес электронной почты клиента.
address_id идентификатор адреса (ВК).
active идикатор активности клиента (0/1).
create_date дата и время добавления в базу данных.
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)

FOREIGN KEY (store_id) REFERENCES store(store_id)
FOREIGN KEY (address_id) REFERENCES address(address_id)

film - таблица фильмов

film_id уникальный идентификатор записи (ПК).
title название фильма.
description краткое описание или сюжет фильма.
release_year год выхода фильма.
language_id id языка фильма (ВК).
original_language_id id языка оригинала фильма в случае, если фильм дублирован.
rental_duration продолжительность периода аренды в днях.
rental_rate стоимость проката фильма на период, указанный в столбце rental_duration.
length продолжительность фильма в минутах.
replacement_cost штраф за утерю или порчу диска.
rating рейтинг, присвоенный фильму. Может быть одним из: G, PG, PG-13, R или NC-17.
special_features список общих специальных функций, включенных в DVD. Может быть ноль или более: трейлеры, комментарии, удаленные сцены, за кадром.
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)

FOREIGN KEY (language_id) REFERENCES language(language_id)
FOREIGN KEY (original_language_id) REFERENCES language(language_id)

film_actor - отношение актеров и фильмов

actor_id идентификатор актера (ВК).
film_id идентификатор фильма (ВК).
last_update дата и время последнего изменения.

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

PRIMARY KEY, btree (actor_id, film_id)

FOREIGN KEY (actor_id) REFERENCES actor(actor_id)
FOREIGN KEY (film_id) REFERENCES film(film_id)

film_category - отношение фильмов к категориям

film_id идентификатор фильма (ВК).
category_id идентификатор категории (ВК).
last_update дата и время последнего изменения.

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

PRIMARY KEY, btree (film_id, category_id)

FOREIGN KEY (film_id) REFERENCES film(film_id)
FOREIGN KEY (category_id) REFERENCES category(category_id)

inventory - список дисков в филиалах компании

inventory_id уникальный идентификатор записи (ПК).
film_id идентификатор фильма (ВК).
store_id id филиала, где находится диск (ВК).
last_update дата и время последнего изменения.

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

PRIMARY KEY, btree (inventory_id)

FOREIGN KEY (film_id) REFERENCES film(film_id)
FOREIGN KEY (store_id) REFERENCES store(store_id)

language - языки фильмов

language_id уникальный идентификатор записи (ПК).
name название языка.
last_update дата и время последнего изменения.

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

PRIMARY KEY, btree (language_id)

payment - платежи клиентов

payment_id уникальный идентификатор записи (ПК).
customer_id идентификатор клиента (ВК).
staff_id id сотрудника принявшего платёж (ВК).
rental_id идентификатор записи аренды (ВК).
amount сумма платежа.
payment_date дата и время платежа.
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)

FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
FOREIGN KEY (rental_id) REFERENCES rental(rental_id)

rental - таблица аренды дисков

rental_id уникальный идентификатор записи (ПК).
rental_date дата начала аренды.
inventory_id идентификатор диска (ВК).
customer_id идентификатор клиента (ВК).
return_date дата возврата фильма.
staff_id id сотрудника выдавшего диск (ВК).
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)

FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id)
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
FOREIGN KEY (staff_id) REFERENCES staff(staff_id)

staff - сотрудники компании

staff_id уникальный идентификатор записи (ПК).
first_name имя сотрудника.
last_name фамилия сотрудника.
address_id идентификатор адреса (ВК).
picture фотография сотрудника.
email адрес электронной почты сотрудника.
store_id id филиала (ВК).
active идикатор активности сотрудника (0/1).
username имя пользователя для входа в систему.
password пароль для входа.
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)

FOREIGN KEY (address_id) REFERENCES address(address_id)
FOREIGN KEY (store_id) REFERENCES store(store_id)

store - филиалы компании

store_id уникальный идентификатор записи (ПК).
manager_staff_id id менеджера магазина (ВК).
address_id id адреса (ВК).
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)

FOREIGN KEY (manager_staff_id) REFERENCES staff(staff_id)
FOREIGN KEY (address_id) REFERENCES address(address_id)

Практика

Уроки

Песочница

Тест

Анализ стоимости проката фильма по категории

Войти

Регистрация

Анализ стоимости проката фильма по категории