Практика: подзапросы в WHERE

Решите несколько задач самостоятельно. Напомним, как использовать подзапросы в WHERE.

Схема базы данных

Чтобы было удобнее решать задачи, скачайте PDF-версию ER-диаграммы с описанием таблиц. Или откройте диаграмму в соседней вкладке.

Задача 6 / 6

Вы уже сравнивали выручку в разных странах, но теперь можно усовершенствовать запросы.

Напишите код для первого подзапроса. Таблица invoice_line хранит информацию о купленных треках. Выгрузите из неё только те заказы (поле invoice_id), которые включают больше пяти треков.

Подсказка

Теперь напишите код для второго подзапроса. С помощью той же таблицы (invoice_line) найдите среднее значение цены одного трека (поле unit_price).

Подсказка

Для каждой страны (поле billing_country) посчитайте минимальное, максимальное и среднее значение выручки из поля total. Назовите поля так: min_total, max_total и avg_total. Нужные поля для выгрузки хранит таблица invoice.

При подсчёте учитывайте только те заказы, которые включают более пяти треков. Стоимость заказа должна превышать среднюю цену одного трека. Используйте код, написанный в предыдущих заданиях.

Отсортируйте итоговую таблицу по значению в поле avg_total от большего к меньшему.

Подсказка

Отберите десять самых коротких по продолжительности треков и выгрузите названия их жанров.

Подсказка

Выгрузите уникальные названия городов, в которых стоимость заказов превышает среднее значение за 2009 год.

Подсказка

Найдите возрастной рейтинг с самыми дорогими для аренды фильмами. Для этого посчитайте среднюю стоимость аренды фильма каждого рейтинга. Выведите на экран названия категорий фильмов с этим рейтингом. Добавьте второе поле со средним значением продолжительности фильмов.

Подсказка

Отберите нужный рейтинг с помощью ORDER BY и LIMIT. Чтобы вывести категории фильмов, объедините несколько таблиц.

xxxxxxxxxx
 
SELECT c.name, AVG(m.length)
FROM category AS c
INNER JOIN film_category AS fc ON c.category_id = fc.category_id
INNER JOIN movie AS m ON m.film_id = fc.film_id
WHERE m.rating = 'PG'
GROUP BY c.name;
--IN (
 --   SELECT DISTINCT(m.rating), AVG(m.rental_rate)
--FROM movie AS m
--GROUP BY m.rating
--ORDER BY AVG(m.rental_rate) DESC
--LIMIT 1;
--);

name	avg
Family	114.556
Games	126.2
Animation	102.273
Classics	100.9
Documentary	92.5
Sports	137.938
New	106.556
Children	107.4
Music	118.7
Travel	110.071
Foreign	121.357
Drama	118.286
Horror	115.333
Action	111
Sci-Fi	94.8333
Comedy	116.812