Решение модуля 1.7 из курса «Интерактивный тренажер по SQL»

Разобрали ответы и решения урока 1.7(Таблица «Нарушения ПДД», запросы корректировки) под курс «Интерактивный тренажер по SQL».

Предыдущий разбор модуля 1.6 следующий разбор модуль 2.1

Создать таблицу fine следующей структуры:

fine_id
ключевой столбец целого типа с автоматическим увеличением значения ключа на 1
name
строка длиной 30
number_plate
строка длиной 6
violation
строка длиной 50
sum_fine
вещественное число, максимальная длина 8, количество знаков после запятой 2
date_violation
дата
date_payment
дата

CREATE TABLE fine
(
    fine_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30),
    number_plate VARCHAR(6),
    violation VARCHAR(30),
    sum_fine DECIMAL(8,2),
    date_violation DATE,
    date_payment DATE
)

В таблицу fine первые 5 строк уже занесены. Добавить в таблицу записи с ключевыми значениями 6, 7, 8.

INSERT INTO fine
(name, number_plate, violation, date_violation)
VALUES
('Баранов П.Е.', 'Р523ВТ', 'Превышение скорости(от 40 до 60)', '2020-02-14'),
('Абрамова К.А.', 'О111АВ', 'Проезд на запрещающий сигнал', '2020-02-23'),
('Яковлев Г.Р.', 'Т330ТТ', 'Проезд на запрещающий сигнал', '2020-03-03');

SELECT * FROM fine;

Занести в таблицу fine суммы штрафов, которые должен оплатить водитель, в соответствии с данными из таблицы traffic_violation. При этом суммы заносить только в пустые поля столбца  sum_fine.

UPDATE fine f, 
    traffic_violation tv
SET f.sum_fine = tv.sum_fine
WHERE f.violation = tv.violation 
    AND f.sum_fine IS NULL;

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

SELECT name, number_plate, violation
FROM fine
GROUP by name, number_plate, violation
HAVING count(violation) >= 2
ORDER by name, number_plate, violation

В таблице fine увеличить в два раза сумму неоплаченных штрафов для отобранных на предыдущем шаге записей. 

UPDATE fine AS f, (
	   SELECT name, number_plate, violation
  		 FROM fine
 		GROUP BY name, number_plate, violation
	   HAVING count(*) >= 2
	   ) AS dv
   SET f.sum_fine = f.sum_fine*2
 WHERE f.date_payment IS Null
	   AND (f.name = dv.name
	   AND f.violation = dv.violation);

SELECT * FROM fine;

Водители оплачивают свои штрафы. В таблице payment занесены даты их оплаты:
Необходимо:
— в таблицу fine занести дату оплаты соответствующего штрафа из таблицы payment;
— уменьшить начисленный штраф в таблице fine в два раза (только для тех штрафов, информация о которых занесена в таблицу payment) , если оплата произведена не позднее 20 дней со дня нарушения.

UPDATE fine f, payment p
SET f.date_payment = p.date_payment,
    f.sum_fine = IF(DATEDIFF(f.date_payment, f.date_violation) <= 20, f.sum_fine / 2, f.sum_fine)
WHERE f.name = p.name AND
      f.number_plate = p.number_plate AND
      f.violation = p.violation AND
      f.date_violation = p.date_violation AND
      f.date_payment IS NULL;

SELECT name, violation, sum_fine, date_violation, date_payment
FROM fine;

Создать новую таблицу back_payment, куда внести информацию о неоплаченных штрафах (Фамилию и инициалы водителя, номер машины, нарушение, сумму штрафа  и  дату нарушения) из таблицы fine.

CREATE TABLE back_payment
    (SELECT name, number_plate, violation, sum_fine, date_violation
     FROM fine
     WHERE date_payment IS NULL);
     
SELECT * FROM back_payment

Удалить из таблицы fine информацию о нарушениях, совершенных раньше 1 февраля 2020 года. 

DELETE FROM fine
WHERE date_violation < DATE(20200201) AND date_payment IS NOT NULL;