Pastein: Ниже приведены SQL-запросы для всех 11 заданий лабораторной работы № 5 «Сложные выборки, подзапросы и множестве

Загрузка данных
Ниже приведены SQL-запросы для всех 11 заданий лабораторной работы № 5 «Сложные выборки, подзапросы и множественные операции», оформленные в вашем стиле (без лишних алиасов таблиц).
> Важный момент перед запуском: В заданиях 4 и 11 используется таблица vidy_shtrafov (она не поместилась в ваш самый первый SQL-дамп). Проверьте в phpMyAdmin, как именно в таблице vidy_shtrafov называется столбец с суммой штрафа (в запросах ниже написано summa и ключ id_shtraf — если у вас столбец называется, например, razmer или cena, замените это слово в запросах 4 и 11).
> 
Задание 1
Условие: Для каждого автомобиля вывести марку, модель, количество договоров, суммарную стоимость договоров и среднюю длительность аренды. Включить автомобили, которые ещё не арендовались. Отсортировать по суммарной стоимости по убыванию.
(По условию предметной области суточная стоимость аренды составляет 0,1 % от цены автомобиля: cena * 0.001 * DATEDIFF(data_vozvrata, data_vidachi)).
SELECT marka, model, COUNT(id_dogovor), SUM(cena * 0.001 * DATEDIFF(data_vozvrata, data_vidachi)), AVG(DATEDIFF(data_vozvrata, data_vidachi))
FROM car
LEFT JOIN dogovor ON car.id_car = dogovor.id_car
GROUP BY car.id_car, marka, model
ORDER BY SUM(cena * 0.001 * DATEDIFF(data_vozvrata, data_vidachi)) DESC

(Если преподаватель требует брать сумму не по формуле договора, а из таблицы фактических оплат oplata, добавьте LEFT JOIN oplata ON dogovor.id_dogovor = oplata.id_dogovor и замените SUM(...) на SUM(Summa_oplaty)).
Задание 2
Условие: Вывести марку и модель автомобилей, которые хотя бы раз сдавались в аренду (есть записи в таблице договоров), но на которые ни разу не был оставлен отзыв.
SELECT marka, model
FROM car
WHERE id_car IN (SELECT id_car FROM dogovor)
  AND id_car NOT IN (SELECT id_car FROM otziv)

(Примечание: если в вашей базе у всех 10 автомобилей уже есть отзывы, запрос отработает без ошибок и вернёт пустой результат — это нормально).
Задание 3
Условие: Для каждого автомобиля вывести марку, модель, цену и ценовую категорию. Категорию определить с помощью оператора CASE: До 1 500 000 руб. – «Эконом»; от 1 500 000 до 2 500 000 руб. – «Комфорт»; свыше 2 500 000 руб. – «Бизнес». Отсортировать результат по убыванию цены автомобиля.
SELECT marka, model, cena,
CASE
    WHEN cena < 1500000 THEN 'Эконом'
    WHEN cena <= 2500000 THEN 'Комфорт'
    ELSE 'Бизнес'
END AS 'Ценовая категория'
FROM car
ORDER BY cena DESC

Задание 4
Условие: Вывести клиентов, у которых больше двух договоров и есть хотя бы один штраф. Показать ФИО, количество договоров, сумму договоров, количество и сумму штрафов.
SELECT familiya, imya, otchestvo, COUNT(DISTINCT dogovor.id_dogovor), SUM(cena * 0.001 * DATEDIFF(data_vozvrata, data_vidachi)), COUNT(id_strafa), SUM(summa)
FROM users
JOIN dogovor ON users.id_user = dogovor.id_user
JOIN car ON dogovor.id_car = car.id_car
JOIN shtrafi ON dogovor.id_dogovor = shtrafi.id_dogovor
JOIN vidy_shtrafov ON shtrafi.id_shtraf = vidy_shtrafov.id_shtraf
GROUP BY users.id_user, familiya, imya, otchestvo
HAVING COUNT(DISTINCT dogovor.id_dogovor) > 2 AND COUNT(id_strafa) >= 1

Задание 5
Условие: Вывести автомобили, у которых есть отзывы с оценкой 10 и по договорам которых нет ни одного штрафа.
SELECT marka, model
FROM car
WHERE id_car IN (SELECT id_car FROM otziv WHERE ocenka = 10)
  AND id_car NOT IN (SELECT id_car FROM dogovor JOIN shtrafi ON dogovor.id_dogovor = shtrafi.id_dogovor)

Задание 6
Условие: Для каждого автомобиля вывести последнее ТО, пробег, дату следующего ТО, количество дней до него и статус «Просрочено» / «Сегодня» / «Ожидается».
SELECT marka, model, data, probeg, next_teh_obs, DATEDIFF(next_teh_obs, CURRENT_DATE()),
CASE
    WHEN next_teh_obs < CURRENT_DATE() THEN 'Просрочено'
    WHEN next_teh_obs = CURRENT_DATE() THEN 'Сегодня'
    ELSE 'Ожидается'
END AS 'Статус'
FROM car
JOIN teh_obs ON car.id_car = teh_obs.car

Задание 7
Условие: Для каждого года выпуска вывести до трёх автомобилей с наибольшим количеством отзывов.
SELECT god_vipuska, marka, model, kolichestvo_otzivov
FROM (
    SELECT YEAR(data_vipuska) AS god_vipuska, marka, model, COUNT(id_otziv) AS kolichestvo_otzivov,
    ROW_NUMBER() OVER (PARTITION BY YEAR(data_vipuska) ORDER BY COUNT(id_otziv) DESC) AS nomer
    FROM car
    LEFT JOIN otziv ON car.id_car = otziv.id_car
    GROUP BY car.id_car, YEAR(data_vipuska), marka, model
) AS tablicza
WHERE nomer <= 3

Задание 8
Условие: Вывести по месяцам 2023 года количество договоров, сумму, среднюю стоимость и накопительный итог суммы.
SELECT MONTH(data_zaklucheniya), COUNT(id_dogovor),
SUM(cena * 0.001 * DATEDIFF(data_vozvrata, data_vidachi)),
AVG(cena * 0.001 * DATEDIFF(data_vozvrata, data_vidachi)),
SUM(SUM(cena * 0.001 * DATEDIFF(data_vozvrata, data_vidachi))) OVER (ORDER BY MONTH(data_zaklucheniya))
FROM dogovor
JOIN car ON dogovor.id_car = car.id_car
WHERE YEAR(data_zaklucheniya) = 2023
GROUP BY MONTH(data_zaklucheniya)
ORDER BY MONTH(data_zaklucheniya)

Задание 9
Условие: Вывести единый список клиентов (id, фамилию и имя), которые либо арендовали автомобиль более 2 раз, либо имеют просроченные водительские права (дата окончания действия прав меньше текущей даты). Примечание: добавить третий столбец «Причина» с текстовым комментарием («Многоразовый клиент» или «Просрочены права»).
SELECT users.id_user, familiya, imya, 'Многоразовый клиент' AS 'Причина'
FROM users
JOIN dogovor ON users.id_user = dogovor.id_user
GROUP BY users.id_user, familiya, imya
HAVING COUNT(id_dogovor) > 2
UNION
SELECT users.id_user, familiya, imya, 'Просрочены права' AS 'Причина'
FROM users
JOIN prava ON users.id_user = prava.id_user
WHERE data_okonchaniya < CURRENT_DATE()

Задание 10
Условие: Вывести id договора, марку и модель автомобиля, дату выдачи и дату возврата. Вычислить фактическое количество дней аренды. Оставить в выборке только те договоры, где фактическая аренда составила более 10 дней, и автомобиль был выдан в течение 60 дней после даты его последнего на тот момент тех. обслуживания.
SELECT id_dogovor, marka, model, data_vidachi, data_vozvrata, DATEDIFF(data_vozvrata, data_vidachi)
FROM dogovor
JOIN car ON dogovor.id_car = car.id_car
JOIN teh_obs ON car.id_car = teh_obs.car
WHERE DATEDIFF(data_vozvrata, data_vidachi) > 10
  AND DATEDIFF(data_vidachi, data) BETWEEN 0 AND 60

Задание 11
Условие: Вывести фамилию и имя клиентов, общее количество полученных ими штрафов и суммарный объем штрафов в рублях. Выводить только тех клиентов, у которых есть хотя бы один штраф. Отсортировать данные по убыванию суммарного объема штрафов.
SELECT familiya, imya, COUNT(id_strafa), SUM(summa)
FROM users
JOIN dogovor ON users.id_user = dogovor.id_user
JOIN shtrafi ON dogovor.id_dogovor = shtrafi.id_dogovor
JOIN vidy_shtrafov ON shtrafi.id_shtraf = vidy_shtrafov.id_shtraf
GROUP BY users.id_user, familiya, imya
ORDER BY SUM(summa) DESC

Прогоняйте запросы в phpMyAdmin, делайте скриншоты, и если где-то столбец в vidy_shtrafov называется иначе — пишите, сразу поправим и соберём готовый отчёт!
Больше возможностей при регистрации:
