/*
Сформулируйте SQL запрос для создания таблицы book
*/

CREATE TABLE book(
    book_id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(30),
    author VARCHAR(30),
    price DECIMAL(8, 2),
    amount INT
);

----------------------------------------------
/*
Занесите новую строку в таблицу book (текстовые значения (тип VARCHAR) 
заключать либо в двойные, либо в одинарные кавычки):
*/
INSERT INTO book (title, author, price, amount) 
VALUES ("Мастер и Маргарита", "Булгаков М.А.", "670.99", 3);

-------------------------------------------------
/*
Занесите три последние записи в таблицуbook,  первая запись уже добавлена на предыдущем шаге:
*/
INSERT INTO book(title, author, price, amount) 
VALUES ('Белая гвардия', 'Булгаков М.А.', 540.50, 5), ('Идиот', 'Достоевский Ф.М.', 460.00, 10), ('Братья Карамазовы', 'Достоевский Ф.М.', 799.01, 2);
SELECT * FROM book;

------------------------------------------------------
/*
Выбрать авторов, название книг и их цену из таблицы book
*/
SELECT author, title, price FROM book;

---------------------------------------------------
/*
Выбрать названия книг и авторов из таблицы book, для поля title 
задать имя(псевдоним) Название, для поля author –  Автор.
*/
SELECT title AS Название, author AS Автор
FROM book;

--------------------------------------------------
/*
Для упаковки каждой книги требуется один лист бумаги, цена которого 1 рубль 65 копеек. 
Посчитать стоимость упаковки для каждой книги (сколько денег потребуется, чтобы упаковать 
все экземпляры книги). В запросе вывести название книги, ее количество и стоимость упаковки,
последний столбец назвать pack
*/

SELECT title, amount,
    amount * 1.65 AS pack 
FROM book;

------------------------------------------------------
/*
В конце года цену всех книг на складе пересчитывают – снижают ее на 30%.
Написать SQL запрос, который из таблицы book выбирает названия, авторов, 
количества и вычисляет новые цены книг. Столбец с новой ценой назвать new_price, 
цену округлить до 2-х знаков после запятой.
*/

SELECT title, author, amount,
ROUND(price*0.7,2) AS new_price
FROM book;

------------------------------------------------------
/*
При анализе продаж книг выяснилось, что наибольшей популярностью пользуются книги Михаила Булгакова, 
на втором месте книги Сергея Есенина. Исходя из этого решили поднять цену книг Булгакова на 10%, 
а цену книг Есенина - на 5%. Написать запрос, куда включить автора, название книги и новую цену, 
последний столбец назвать new_price. Значение округлить до двух знаков после запятой.
*/

SELECT author, title,
ROUND(IF(author = "Булгаков М.А.", price * 1.1, IF(author = "Есенин С.А.", price * 1.05, price * 1)),2) AS new_price
FROM book;
--------------------------------------------------------

/*
Вывести автора, название  и цены тех книг, количество которых меньше 10.
*/

SELECT author, title, price
FROM book
WHERE amount < 10;
------------------------------------------------------------------------------

/* 
Вывести название, автора,  цену  и количество всех книг, цена которых меньше 500 или больше 600, 
а стоимость всех экземпляров этих книг больше или равна 5000
*/ 

SELECT title, author, price, amount
FROM book
WHERE (price < 500 OR price > 600) AND price * amount > 5000;
--------------------------------------------------------------------------------

/*
Вывести название и авторов тех книг, цены которых принадлежат интервалу 
от 540.50 до 800 (включая границы),  а количество или 2, или 3, или 5, или 7.
*/

SELECT title, author
FROM book
WHERE amount IN (2, 3, 5, 7) AND price >= 540.50 AND price <= 800;

-------------------------------------------------------------------------------------------------
/*
Вывести  автора и название  книг, количество которых принадлежит интервалу от 2 до 14 (включая границы). 
Информацию  отсортировать сначала по авторам (в обратном алфавитном порядке), а затем по названиям книг 
(по алфавиту).
*/

SELECT author, title
FROM book
WHERE amount >= 2 AND amount <= 14
ORDER BY author DESC, title ASC;
------------------------------------------------------------------------------------

/*Вывести название и автора тех книг, название которых состоит из двух и более слов, 
а инициалы автора содержат букву «С». Считать, что в названии слова отделяются 
друг от друга пробелами и не содержат знаков препинания, между фамилией автора и 
инициалами обязателен пробел, инициалы записываются без пробела в формате: буква, точка, 
буква, точка. Информацию отсортировать по названию книги в алфавитном порядке.
*/
SELECT title, author 
FROM book 
WHERE title LIKE "_% _%" AND author LIKE '%С.%'
ORDER BY title ASC;    

-------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов к нашей таблице book. Проверьте, правильно ли они работают.
*/


INSERT INTO book (title, author, price, amount) 
VALUES ("Империя должна умереть", "Зыгарь М.В.", "999", 666);

SELECT title, author, price, amount,
	amount * price AS total
FROM book
ORDER BY total DESC;

-----------------------------------------------------------------------

/*
Отобрать различные (уникальные) элементы столбца amount таблицы book.
*/

SELECT DISTINCT amount
FROM book;
--------------------------------------------------------------------------
/*
Посчитать, количество различных книг и количество экземпляров книг каждого автора , 
хранящихся на складе.  Столбцы назвать Автор, Различных_книг и Количество_экземпляров 
соответственно.
*/

SELECT author AS Автор, count(amount) AS Различных_книг, sum(amount) AS Количество_экземпляров
FROM book
GROUP BY author;
---------------------------------------------------------------------------------
/*
Вывести фамилию и инициалы автора, минимальную, максимальную и среднюю цену книг каждого автора . 
Вычисляемые столбцы назвать Минимальная_цена, Максимальная_цена и Средняя_цена соответственно.
*/

SELECT author, MIN(price) AS Минимальная_цена, MAX(price) AS Максимальная_цена, AVG(price) AS Средняя_цена
FROM book
GROUP BY author;
------------------------------------------------------------------------------------------------------------
/*
Для каждого автора вычислить суммарную стоимость книг S (имя столбца Стоимость), а также вычислить налог 
на добавленную стоимость  для полученных сумм (имя столбца НДС ) , который включен в стоимость и составляет 
k = 18%,  а также стоимость книг  (Стоимость_без_НДС) без него. Значения округлить до двух знаков после запятой. 
В запросе для расчета НДС(tax)  и Стоимости без НДС(S_without_tax)
*/

SELECT author, 
    ROUND(SUM(price * amount), 2) AS Стоимость, 
    ROUND(SUM((price * amount * 0.18) / 1.18), 2) AS НДС, 
    ROUND(SUM((price * amount) - ((price * amount * 0.18) / 1.18)), 2) AS Стоимость_без_НДС 
FROM book 
GROUP BY author;
----------------------------------------------------------------------------------------------------
/*
Вывести  цену самой дешевой книги, цену самой дорогой и среднюю цену уникальных книг на складе.
Названия столбцов Минимальная_цена, Максимальная_цена, Средняя_цена соответственно. 
Среднюю цену округлить до двух знаков после запятой.
*/

SELECT MIN(price) AS Минимальная_цена, 
    MAX(price) AS Максимальная_цена,
    ROUND(AVG(price), 2) AS Средняя_цена
FROM book;
-----------------------------------------------------------------------------------------------------------
/*
ычислить среднюю цену и суммарную стоимость тех книг, количество экземпляров которых принадлежит интервалу 
от 5 до 14, включительно. Столбцы назвать Средняя_цена и Стоимость, значения округлить до 2-х знаков 
после запятой.
*/
SELECT ROUND(AVG(price), 2) AS Средняя_цена,
    ROUND(SUM(price * amount), 2) AS Стоимость
FROM book
-- GROUP BY amount
-- HAVING amount BETWEEN 5 AND 14
WHERE amount BETWEEN 5 AND 14;
------------------------------------------------------------------------------------------------------------------
/*
Посчитать стоимость всех экземпляров каждого автора без учета книг «Идиот» и «Белая гвардия». В результат включить 
только тех авторов, у которых суммарная стоимость книг (без учета книг «Идиот» и «Белая гвардия») более 5000 руб. 
Вычисляемый столбец назвать Стоимость. Результат отсортировать по убыванию стоимости.
*/

SELECT author,
    SUM(price * amount) AS Стоимость
FROM book
-- WHERE title NOT IN ('Идиот', 'Белая гвардия') -- ok
WHERE title <> 'Идиот' AND title <> 'Белая гвардия'
GROUP BY author
HAVING SUM(price * amount) > 5000
ORDER BY SUM(price * amount) DESC;
-------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов к нашей таблице book, используя групповые функции. Проверьте, правильно ли они работают.
*/

SELECT ROUND(AVG(price), 2) AS Средняя_цена,
    ROUND(SUM(price * amount), 2) AS Стоимость
    
FROM book
WHERE author <> 'Достоевский Ф.М.'
GROUP BY author
HAVING MAX(price * amount) > 2000
ORDER BY MAX(price * amount) DESC;
---------------------------------------------------------------------------------------------------------------------
/*
Вывести информацию (автора, название и цену) о  книгах, цены которых меньше или равны средней цене книг на складе. 
Информацию вывести в отсортированном по убыванию цены виде. Среднее вычислить как среднее по цене книги.
*/

SELECT author, title, price
FROM book
WHERE price <= (
    SELECT  AVG(price)
    FROM book
    )
ORDER BY price DESC;
-----------------------------------------------------------------------------------------------------------------------
/*
Вывести информацию (автора, название и цену) о тех книгах, цены которых превышают минимальную цену книги на складе не 
более чем на 150 рублей в отсортированном по возрастанию цены виде.
*/

SELECT author, title, price
FROM book

WHERE ABS(price - (
    SELECT  MIN(price) 
    FROM book
    )) <= 150
    
ORDER BY price ASC;
------------------------------------------------------------------------------------------------------------------------
/*
Вывести информацию (автора, книгу и количество) о тех книгах, количество экземпляров которых в таблице book не дублируется.
*/

SELECT author, title, amount
FROM book
WHERE amount IN (
        SELECT amount 
        FROM book 
        GROUP BY amount 
        HAVING COUNT(amount) = 1
    );
-------------------------------------------------------------------------------------------------------------------------
/*
Вывести информацию о книгах(автор, название, цена), цена которых меньше самой большой из минимальных цен, 
вычисленных для каждого автора.
*/

SELECT author, title, price
FROM book
WHERE price < ALL (
        SELECT MAX(price)
        FROM book
        GROUP BY author 
    );
-----------------------------------------------------------------------------------------------------------------------------------
/*
Посчитать сколько и каких экземпляров книг нужно заказать поставщикам, чтобы на складе стало одинаковое количество экземпляров 
каждой книги, равное значению самого большего количества экземпляров одной книги на складе. Вывести название книги, ее автора, 
текущее количество экземпляров на складе и количество заказываемых экземпляров книг. Последнему столбцу присвоить имя Заказ. 
В результат не включать книги, которые заказывать не нужно.
*/

SELECT title, author, amount,
    FLOOR((SELECT max(amount) FROM book)) - amount AS Заказ 
FROM book
WHERE ABS(amount - (SELECT MAX(amount) FROM book)) > 0;

-------------------------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов к нашей таблице book, используя вложенные запросы. 
*/
SELECT title, author, amount,
    ABS((SELECT AVG(amount) FROM book))  AS eeeeee 
FROM book
WHERE author LIKE "%А.%" AND amount > 5;

---------------------------------------------------------------------------------------------------------------------------------------
/*
Создать таблицу поставка (supply), которая имеет ту же структуру, что и таблиц book.

*/

CREATE TABLE supply(supply_id INT PRIMARY KEY AUTO_INCREMENT, 
               title VARCHAR(50), 
               author VARCHAR(30), 
               price DECIMAL(8,2), 
               amount INT);
----------------------------------------------------------------------------------------------------------------------------------------
/*
Занесите в таблицу supply четыре записи, чтобы получилась следующая таблица:
supply_id	title	author	price	amount
1	Лирика	Пастернак Б.Л.	518.99	2
2	Черный человек 	Есенин С.А.	570.20	6
3	Белая гвардия	Булгаков М.А.	540.50	7
4	Идиот	Достоевский Ф.М.	360.80	3
*/

INSERT INTO supply (title, author, price, amount)
VALUES 
    ('Лирика', 'Пастернак Б.Л.', 518.99, 2),
    ('Черный человек', 'Есенин С.А.', 570.20, 6),
    ('Белая гвардия', 'Булгаков М.А.', 540.50, 7),
    ('Идиот', 'Достоевский Ф.М.', 360.80, 3);
SELECT * FROM supply;
----------------------------------------------------------------------------------------------------------------------------------------
/*
Добавить из таблицы supply в таблицу book, все книги, кроме книг, написанных Булгаковым М.А. и Достоевским Ф.М.
*/

INSERT INTO book (title, author, price, amount) 
SELECT title, author, price, amount 
FROM supply 
    WHERE author NOT LIKE "Булг%" AND author NOT LIKE "Дост%"; 

SELECT * FROM book;

----------------------------------------------------------------------------------------------------------------------------------------
/*

*/

INSERT INTO book (title, author, price, amount) 
SELECT title, author, price, amount 
FROM supply 
    WHERE author NOT IN (SELECT author FROM book); 

SELECT * FROM book;
-----------------------------------------------------------------------------------------------------------------------------------------
/*
Уменьшить на 10% цену тех книг в таблице book, количество которых принадлежит интервалу от 5 до 10, включая границы.
*/

UPDATE book 
SET price = 0.9 * price
WHERE 5 <= amount AND amount <= 10;

SELECT * FROM book;
---------------------------------------------------------------------------------------------------------------------------------------
/*
В таблице book необходимо скорректировать значение для покупателя в столбце buy таким образом, чтобы оно не превышало количество 
экземпляров книг, указанных в столбце amount. А цену тех книг, которые покупатель не заказывал, снизить на 10%
*/

UPDATE 
    book 
SET 
    buy = IF(buy >= amount, amount, buy), 
    price = IF(buy = 0, 0.9 * price, price);
SELECT 
    * 
FROM 
    book;
----------------------------------------------------------------------------------------------------------------------------------------
/*
Для тех книг в таблице book , которые есть в таблице supply, не только увеличить их количество в таблице book ( увеличить их количество 
на значение столбца amountтаблицы supply), но и пересчитать их цену (для каждой книги найти сумму цен из таблиц book и supply и разделить 
на 2)
*/

UPDATE 
  book, 
  supply 
SET 
  book.amount = book.amount + supply.amount, 
  book.price = (book.price + supply.price) / 2 
WHERE 
  book.title = supply.title 
  AND 
  book.author = supply.author;
  
SELECT 
  * 
FROM 
  book;
----------------------------------------------------------------------------------------------------------------------------------------
/*
Удалить из таблицы supply книги тех авторов, общее количество экземпляров книг которых в таблице book превышает 10.
*/
DELETE FROM 
  supply 
WHERE 
  author IN (
    SELECT 
      author 
    FROM 
      book 
    GROUP BY 
      author 
    HAVING 
      SUM(amount) > 10
  );
SELECT 
  * 
FROM 
  supply;
----------------------------------------------------------------------------------------------------------------------------------------
/*
Создать таблицу заказ (ordering), куда включить авторов и названия тех книг, количество экземпляров которых в таблице book меньше среднего
количества экземпляров книг в таблице book. В таблицу включить столбец   amount, в котором для всех книг указать одинаковое значение - 
среднее количество экземпляров книг в таблице book.

*/

CREATE TABLE ordering AS
SELECT
    author,
    title,
    (SELECT
    ROUND(AVG(amount))
    FROM book
    ) AS amount

FROM book
    WHERE amount < (SELECT AVG(amount) FROM book);

SELECT 
*
FROM ordering;
----------------------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов корректировки данных к  таблицамbook и  supply . Проверьте, правильно ли они работают.
*/

UPDATE book
SET book.price = book.price * 0.5
WHERE amount < 5;
SELECT * FROM book;

DELETE FROM supply 
WHERE author LIKE 'Дост%' OR amount < 2;
SELECT * FROM supply;

-----------------------------------------------------------------------------------------------------------------------------------
/*
Вывести из таблицы trip информацию о командировках тех сотрудников, фамилия которых заканчивается на букву «а», в отсортированном 
по убыванию даты последнего дня командировки виде. В результат включить столбцы name, city, per_diem, date_first, date_last
*/

SELECT name, city, per_diem, date_first, date_last 
FROM trip 
WHERE name LIKE '%а %'
ORDER BY date_last DESC;

-------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести в алфавитном порядке фамилии и инициалы тех сотрудников, которые были в командировке в Москве.
*/

SELECT DISTINCT name
FROM trip
WHERE city="Москва"
ORDER BY name ASC;
-------------------------------------------------------------------------------------------------------------------------------------
/*
Для каждого города посчитать, сколько раз сотрудники в нем были.  Информацию вывести в отсортированном в алфавитном порядке по 
названию городов. Вычисляемый столбец назвать Количество.
*/

SELECT city, COUNT(name) AS Количество
FROM trip
GROUP BY city
ORDER BY city ASC;
---------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести два города, в которых чаще всего были в командировках сотрудники. Вычисляемый столбец назвать Количество.
*/

SELECT city, COUNT(city) AS Количество
FROM trip
GROUP BY city
ORDER BY Количество DESC
LIMIT 2;
--------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести информацию о командировках во все города кроме Москвы и Санкт-Петербурга (фамилии и инициалы сотрудников, город ,  
длительность командировки в днях, при этом первый и последний день относится к периоду командировки). Последний столбец назвать 
Длительность. Информацию вывести в упорядоченном по убыванию длительности поездки, а потом по убыванию названий городов (в обратном 
алфавитном порядке).
*/

SELECT name, city, DATEDIFF(date_last, date_first)+1 AS Длительность
FROM trip
WHERE city NOT IN ("Москва", "Санкт-Петербург")
ORDER BY Длительность DESC;
--------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести информацию о командировках сотрудника(ов), которые были самыми короткими по времени. 
В результат включить столбцы name, city, date_first, date_last.
*/

SELECT name, city, date_first, date_last
FROM trip
WHERE DATEDIFF(date_last, date_first) IN (SELECT MIN(DATEDIFF(date_last, date_first)) FROM trip)
;
---------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести информацию о командировках, начало и конец которых относятся к одному месяцу (год может быть любой). 
В результат включить столбцы name, city, date_first, date_last. Строки отсортировать сначала  в алфавитном порядке 
по названию города, а затем по фамилии сотрудника .
*/

SELECT name, city, date_first, date_last
FROM trip
WHERE MONTH(date_first) = MONTH(date_last)
ORDER BY city, name ASC;
---------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести название месяца и количество командировок для каждого месяца. Считаем, что командировка относится к некоторому месяцу, 
если она началась в этом месяце. Информацию вывести сначала в отсортированном по убыванию количества, а потом в алфавитном 
порядке по названию месяца виде. Название столбцов – Месяц и Количество.
*/

SELECT MONTHNAME(date_first) AS Месяц,
       COUNT(MONTH(date_first)) AS Количество
FROM trip

GROUP BY 1
ORDER BY 2 DESC, 1;
-------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести сумму суточных (произведение количества дней командировки и размера суточных) для командировок, первый день которых пришелся 
на февраль или март 2020 года. Значение суточных для каждой командировки занесено в столбец per_diem. Вывести фамилию и инициалы 
сотрудника, город, первый день командировки и сумму суточных. Последний столбец назвать Сумма. Информацию отсортировать сначала  
в алфавитном порядке по фамилиям сотрудников, а затем по убыванию суммы суточных.
*/
SELECT name, 
       city,
       date_first,
       per_diem * (DATEDIFF(date_last, date_first)+1) AS Сумма
       
FROM trip
WHERE EXTRACT(MONTH from date_first) in (2, 3)
ORDER BY 1, 4 DESC;
-------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести фамилию с инициалами и общую сумму суточных, полученных за все командировки для тех сотрудников, которые были в командировках 
больше чем 3 раза, в отсортированном по убыванию сумм суточных виде. Последний столбец назвать Сумма
*/

SELECT name, 
      SUM(per_diem * (DATEDIFF(date_last, date_first)+1)) AS Сумма
  
FROM trip

WHERE name IN (
        SELECT name 
        FROM trip 
        GROUP BY name 
        HAVING COUNT(name) > 3
      )
GROUP by 1 
ORDER by 2 DESC
;
-------------------------------------------------------------------------------------------------------------------------------------------
/*
Создать таблицу 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(50),
    sum_fine decimal(8, 2),
    date_violation date,
    date_payment date
    );
--------------------------------------------------------------------------------------------------------------------------------------------
/*
В таблицу fine первые 5 строк уже занесены. Добавить в таблицу записи с ключевыми значениями 6, 7, 8.
*/

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

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

update fine as f, traffic_violation as tv
set f.sum_fine = tv.sum_fine
where f.sum_fine is Null and f.violation = tv.violation;
select * from fine
;
----------------------------------------------------------------------------------------------------------------------------------------------
/*
Вывести фамилию, номер машины и нарушение только для тех водителей, которые на одной машине нарушили одно и то же правило   два и более раз. 
При этом учитывать все нарушения, независимо от того оплачены они или нет. Информацию отсортировать в алфавитном порядке, 
сначала по фамилии водителя, потом по номеру машины и, наконец, по нарушению.
*/

select name, number_plate, violation
from fine
group by 1, 2, 3
having count(*) >=2
order by 1
;
--------------------------------------------------------------------------------------------------------------------------------------------
/*
В таблице fine увеличить в два раза сумму неоплаченных штрафов для отобранных на предыдущем шаге записей. 
*/
UPDATE 
    fine as f, 
    (select name, number_plate, violation
     from fine
     group by name, number_plate, violation
     having count(*) > 1
     ) as query_in
SET 
    f.sum_fine = f.sum_fine * 2
WHERE 
    f.date_payment is Null AND
    f.name = query_in.name AND
    f.number_plate = query_in.number_plate AND
    f.violation = query_in.violation
;
------------------------------------------------------------------------------------------------------------------------------------------
/*
Необходимо:

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

UPDATE 
    fine as f, payment as pm
set
    f.date_payment = pm.date_payment,
    f.sum_fine = if(datediff(pm.date_payment, f.date_violation) <= 20, f.sum_fine/2, f.sum_fine)

WHERE
    f.name = pm.name and
    f.number_plate = pm.number_plate and
    f.violation = pm.violation and
    f.date_payment is Null 
;

SELECT * 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 < '2020-02-1' 
;
SELECT 
  * 
FROM 
  fine;
---------------------------------------------------------------------------------------------------------------
/*
Создать таблицу author следующей структуры:

Поле		Тип, описание
author_id	INT PRIMARY KEY AUTO_INCREMENT
name_author	VARCHAR(50)
*/
create table author (
    author_id int primary key auto_increment,
    name_author varchar(50));
---------------------------------------------------------------------------------------------------------------
/*
Заполнить таблицу author. В нее включить следующих авторов:
Булгаков М.А.
Достоевский Ф.М.
Есенин С.А.
Пастернак Б.Л.
*/
insert into author (name_author)
values('Булгаков М.А.'), 
('Достоевский Ф.М.'),
('Есенин С.А.'), 
('Пастернак Б.Л.');

--------------------------------------------------------------------------------------------------------------
/*
Перепишите запрос на создание таблицы book , чтобы ее структура соответствовала структуре, показанной 
на логической схеме (таблица genre уже создана, порядок следования столбцов - как на логической схеме в 
таблице book, genre_id  - внешний ключ) . Для genre_id ограничение о недопустимости пустых значений не задавать. 
В качестве главной таблицы для описания поля  genre_idиспользовать таблицу genre следующей структуры:

Поле		Тип, описание
genre_id	INT PRIMARY KEY AUTO_INCREMENT
name_genre	VARCHAR(30)
*/

CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(50),
    author_id INT NOT NULL,
    genre_id INT,
    price DECIMAL(8,2), 
    amount INT,
    FOREIGN KEY (author_id)  REFERENCES author (author_id),
    FOREIGN KEY (genre_id)  REFERENCES genre (genre_id)
    
);
--------------------------------------------------------------------------------------------------------------
/*
Создать таблицу book той же структуры, что и на предыдущем шаге. Будем считать, что при удалении автора из 
таблицы author, должны удаляться все записи о книгах из таблицы book, написанные этим автором. А при удалении 
жанра из таблицы genre для соответствующей записи book установить значение Null в столбце genre_id. 
*/

CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(50),
    author_id INT NOT NULL,
    genre_id INT,
    price DECIMAL(8,2), 
    amount INT,
    FOREIGN KEY (author_id) REFERENCES author (author_id) ON DELETE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genre (genre_id) ON DELETE SET NULL
    
);
---------------------------------------------------------------------------------------------------------------
/*
Добавьте три последние записи (с ключевыми значениями 6, 7, 8) в таблицу book, первые 5 записей уже добавлены:
*/

insert into book (title, author_id, genre_id, price, amount)
values
('Стихотворения и поэмы', 3, 2, 650.00, 15),
('Черный человек', 3, 2, 570.20, 6),
('Лирика', 4, 2, 518.99, 2);

select * from book;
---------------------------------------------------------------------------------------------------------------
/*
Вывести название, жанр и цену тех книг, количество которых больше 8, в отсортированном по убыванию цены виде.
*/
select title, name_genre, price
from
    book inner join genre
    on book.genre_id = genre.genre_id 
--    and amount > 8
    where book.amount >= 8
order by price desc
;
---------------------------------------------------------------------------------------------------------------
/*
Вывести все жанры, которые не представлены в книгах на складе.
*/

select name_genre
from
    genre left join book
    on book.genre_id = genre.genre_id 
where title is null
order by price desc
;
---------------------------------------------------------------------------------------------------------------
/*
Необходимо в каждом городе провести выставку книг каждого автора в течение 2020 года. Дату проведения выставки 
выбрать случайным образом. Создать запрос, который выведет город, автора и дату проведения выставки. Последний 
столбец назвать Дата. Информацию вывести, отсортировав сначала в алфавитном порядке по названиям городов, 
а потом по убыванию дат проведения выставок.
*/

SELECT name_city, 
       name_author, 
       DATE_ADD('2020-01-01', INTERVAL RAND() * 365 DAY) as Дата
FROM
    city cross join author
order by name_city, Дата desc 
;
----------------------------------------------------------------------------------------------------------------
/*
 Вывести информацию о книгах (жанр, книга, автор), относящихся к жанру, включающему слово «роман» в 
 отсортированном по названиям книг виде.
*/

SELECT name_genre, title, name_author
FROM
    author 
    INNER JOIN  book ON author.author_id = book.author_id
    INNER JOIN genre ON genre.genre_id = book.genre_id
 where name_genre like '%роман%'
order by title asc
;
----------------------------------------------------------------------------------------------------------------
/*
Посчитать количество экземпляров  книг каждого автора из таблицы author.  Вывести тех авторов,  
количество книг которых меньше 10, в отсортированном по возрастанию количества виде. Последний 
столбец назвать Количество.
*/

SELECT name_author, sum(amount) AS Количество
FROM 
    author LEFT JOIN book
    on author.author_id = book.author_id

GROUP BY 1
having sum(amount) < 10 OR sum(amount) is null 
ORDER BY 2
;  
----------------------------------------------------------------------------------------------------------------
/*
Вывести в алфавитном порядке всех авторов, которые пишут только в одном жанре. Поскольку у нас в таблицах так 
занесены данные, что у каждого автора книги только в одном жанре,  для этого запроса внесем изменения в таблицу 
book. Пусть у нас  книга Есенина «Черный человек» относится к жанру «Роман», а книга Булгакова «Белая гвардия» 
к «Приключениям» (эти изменения в таблицы уже внесены).
*/

select name_author
from
    author left join book
    on author.author_id = book.author_id
GROUP BY 1
having count(distinct(genre_id)) = 1
ORDER BY 1
;
----------------------------------------------------------------------------------------------------------------
/*
Вывести информацию о книгах (название книги, фамилию и инициалы автора, название жанра, цену и количество 
экземпляров книги), написанных в самых популярных жанрах, в отсортированном в алфавитном порядке по названию 
книг виде. Самым популярным считать жанр, общее количество экземпляров книг которого на складе максимально.
*/

select title, name_author, name_genre, price, amount
from
    author 
    inner join book on author.author_id = book.author_id
    inner join genre on book.genre_id = genre.genre_id
    
GROUP BY title, name_author, name_genre, price, amount, genre.genre_id
having genre.genre_id in
         (/* выбираем автора, если он пишет книги в самых популярных жанрах*/
          SELECT query_in_1.genre_id
          FROM 
              (/* выбираем код жанра и количество произведений, относящихся к нему */
                SELECT genre_id, SUM(amount) AS sum_amount
                FROM book
                GROUP BY genre_id
               ) as query_in_1
          INNER JOIN 
              (/* выбираем запись, в которой указан код жанр с максимальным количеством книг */
                SELECT genre_id, SUM(amount) AS sum_amount
                FROM book
                GROUP BY genre_id
                ORDER BY sum_amount DESC
                LIMIT 1
               ) as query_in_2
          ON query_in_1.sum_amount = query_in_2.sum_amount
         )
order by 1;
-----------------------------------------------------------------------------------------------------------------
/*
Если в таблицах supply  и book есть одинаковые книги, которые имеют равную цену,  вывести их название и автора, 
а также посчитать общее количество экземпляров книг в таблицах supply и book,  столбцы назвать Название, Автор  
и Количество.
*/

SELECT book.title as Название, name_author as Автор, (book.amount + supply.amount) as Количество
FROM author
    INNER JOIN book USING (author_id)   
    INNER JOIN supply ON book.title = supply.title 
    and author.name_author = supply.author
where supply.price = book.price
;
------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов для таблиц book,  author, genre и city. Проверьте, правильно ли они работают.
*/

select 
    book.title,
    name_author, 
    book.amount,
    book.price,
    genre.name_genre,
    round (sum(price*amount)) as sum_price
from
    author 
    inner join book USING (author_id) 
    inner join genre using (genre_id)
    
group by 1, 2, 3, 4, 5

order by 3 desc
;
------------------------------------------------------------------------------------------------------------------
/*
Для книг, которые уже есть на складе (в таблице book), но по другой цене, чем в поставке (supply),  
необходимо в таблице book увеличить количество на значение, указанное в поставке,  и пересчитать цену. 
А в таблице  supply обнулить количество этих книг
*/

UPDATE 
  book 
  INNER JOIN author ON author.author_id = book.author_id 
  INNER JOIN supply ON book.title = supply.title 
  and supply.author = author.name_author 
SET 
  book.price = (
    book.amount * book.price + supply.amount * supply.price
  ) / (book.amount + supply.amount), 
  book.amount = book.amount + supply.amount, 
  supply.amount = 0 
WHERE 
  book.price <> supply.price;
SELECT 
  * 
FROM 
  book;
SELECT 
  * 
FROM 
  supply;
-------------------------------------------------------------------------------------------------------------------
/*
Включить новых авторов в таблицу author с помощью запроса на добавление, а затем вывести все данные из таблицы 
author.  Новыми считаются авторы, которые есть в таблице supply, но нет в таблице author.
*/

insert into author (name_author)
SELECT supply.author
FROM author
right join supply on author.name_author = supply.author
WHERE author.name_author is null
;

select * from author;
--------------------------------------------------------------------------------------------------------------------
/*
Добавить новые книги из таблицы supply в таблицу book на основе сформированного выше запроса. Затем вывести 
для просмотра таблицу book.
*/

insert into book(title, author_id, price, amount)
SELECT title, author_id, price, amount
FROM
    author
    INNER JOIN supply ON author.name_author = supply.author
WHERE supply.amount <> 0;

select * from supply;
----------------------------------------------------------------------------------------------------------
/*
 Занести для книги «Стихотворения и поэмы» Лермонтова жанр «Поэзия», а для книги «Остров сокровищ» 
 Стивенсона - «Приключения». (Использовать два запроса).
*/

UPDATE book

SET genre_id = 
      (
       SELECT genre_id 
       FROM genre 
       WHERE name_genre = 'Поэзия'
      )
WHERE book_id = 10;

UPDATE book
SET genre_id = 
      (
       SELECT genre_id 
       FROM genre 
       WHERE name_genre = 'Приключения'
      )
WHERE book_id = 11
;

SELECT * FROM book;
---------------------------------------------------------------------------------------------------------------------
/*
Удалить всех авторов и все их книги, общее количество книг которых меньше 20.
*/

DELETE FROM author
where author_id in (
    select author_id
    from book
    group by author_id
    having sum(amount) < 20
    )
;

SELECT * FROM book;
---------------------------------------------------------------------------------------------------------------------
/*
Удалить все жанры, к которым относится меньше 4-х книг. В таблице book для этих жанров установить значение Null.
*/

DELETE FROM genre
WHERE genre_id in (select genre_id
                   from book
                   group by genre_id
                   having count(amount) < 4)
;

select * from book;
select * from genre;
-----------------------------------------------------------------------------------------------------------------------
/*
Удалить всех авторов, которые пишут в жанре "Поэзия". Из таблицы book удалить все книги этих авторов. В запросе для 
отбора авторов использовать полное название жанра, а не его id.
*/

DELETE FROM author
USING 
    author 
    INNER JOIN book ON author.author_id = book.author_id
    INNER JOIN genre ON book.genre_id = genre.genre_id
WHERE genre.name_genre = 'Поэзия'
;

SELECT * FROM author;
SELECT * FROM book;
------------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов корректировки данных для таблиц book,  author, genre и supply . 
Проверьте, правильно ли они работают.
*/

--------1-------------------
insert into author (name_author) 
select 
    supply.author 
FROM 
    author 
    right join supply on author.name_author = supply.author;
select * from author;
--------2-------------------
insert into book(title, author_id, price, amount)
SELECT title, author_id, price, amount
FROM
    supply
    inner join author ON author.name_author = supply.author
where title not in (select title from book);
select * from book;
--------3-------------------
update book 
set genre_id = 3 
where 
  title = 'Доктор Живаго';

update book 
set genre_id = 1 
where 
  title = 'Остров сокровищ';
select * from book;
-------------------------------------------------------------------------------------------------------------------------
/*
Вывести все заказы Баранова Павла (id заказа, какие книги, по какой цене и в каком количестве он заказал) 
в отсортированном по номеру заказа и названиям книг виде.
*/

SELECT DISTINCT buy_book.buy_id, book.title, book.price, buy_book.amount
FROM 
    client 
    INNER JOIN buy ON client.client_id = buy.client_id
    INNER JOIN buy_book ON buy_book.buy_id = buy.buy_id
    INNER JOIN book ON buy_book.book_id=book.book_id
WHERE name_client like 'Баранов Павел'
order by 1, 2 asc
;
-------------------------------------------------------------------------------------------------------------------------
/*
Посчитать, сколько раз была заказана каждая книга, для книги вывести ее автора (нужно посчитать, в каком количестве 
заказов фигурирует каждая книга).  Вывести фамилию и инициалы автора, название книги, последний столбец назвать Количество. 
Результат отсортировать сначала  по фамилиям авторов, а потом по названиям книг.
*/

select author.name_author, book.title, count(buy_book.book_id) as Количество
from book
    inner join author on author.author_id = book.author_id
    left join buy_book on buy_book.book_id = book.book_id
group by 1, 2
order by 1, 2
;

-------------------------------------------------------------------------------------------------------------------------
/*
Вывести города, в которых живут клиенты, оформлявшие заказы в интернет-магазине. Указать количество заказов в каждый 
город, этот столбец назвать Количество. Информацию вывести по убыванию количества заказов, а затем в алфавитном порядке 
по названию городов.
*/
select city.name_city, count(client.client_id) as Количество
from buy
    left join client on client.client_id = buy.client_id
    inner join city on city.city_id = client.city_id
group by 1
order by 2 desc
;
------------------------------------------------------------------------------------------------------------------------
/*
Вывести номера всех оплаченных заказов и даты, когда они были оплачены.
*/

select buy_id, date_step_end
from buy_step
where step_id = 1 and date_step_end is not NULL
;
------------------------------------------------------------------------------------------------------------------------
/*
Вывести информацию о каждом заказе: его номер, кто его сформировал (фамилия пользователя) и его стоимость 
(сумма произведений количества заказанных книг и их цены), в отсортированном по номеру заказа виде. 
Последний столбец назвать Стоимость.
*/

select buy.buy_id, client.name_client, sum(book.price * buy_book.amount) as Стоимость
from buy_book
    inner join book on buy_book.book_id = book.book_id
    inner join buy on buy.buy_id = buy_book.buy_id
    inner join client on client.client_id = buy.client_id
    
group by 1, 2
order by 1
;
-------------------------------------------------------------------------------------------------------------------------
/*
Вывести номера заказов (buy_id) и названия этапов,  на которых они в данный момент находятся. Если заказ доставлен –  
информацию о нем не выводить. Информацию отсортировать по возрастанию buy_id.
*/

select buy_id, name_step
from buy_step
    inner join step using (step_id)

where buy_id <> 1 and date_step_end is null and date_step_beg is not null
order by 1
;
-------------------------------------------------------------------------------------------------------------------------
/*
В таблице city для каждого города указано количество дней, за которые заказ может быть доставлен в этот город 
(рассматривается только этап Транспортировка). Для тех заказов, которые прошли этап транспортировки, вывести количество 
дней за которое заказ реально доставлен в город. А также, если заказ доставлен с опозданием, указать количество дней 
задержки, в противном случае вывести 0. В результат включить номер заказа (buy_id), а также вычисляемые столбцы 
Количество_дней и Опоздание. Информацию вывести в отсортированном по номеру заказа виде.
*/

select buy_id, datediff(date_step_end, date_step_beg) as Количество_дней,
               greatest(datediff(date_step_end, date_step_beg) - days_delivery, 0) as Опоздание
from buy_step
    inner join buy using(buy_id)
    inner join client using(client_id)
    inner join city using(city_id)
where datediff(date_step_end, date_step_beg) > 0 and step_id = 3
order by 1
;
--------------------------------------------------------------------------------------------------------------------------
/*
Выбрать всех клиентов, которые заказывали книги Достоевского, информацию вывести в отсортированном по алфавиту виде. 
В решении используйте фамилию автора, а не его id.
*/

select distinct name_client
from client
    inner join buy using(client_id)
    inner join buy_book using(buy_id)
    inner join book using(book_id)
    inner join author using(author_id)
where name_author like "Дост%"
order by 1
;
--------------------------------------------------------------------------------------------------------------------------
/*
Вывести жанр (или жанры), в котором было заказано больше всего экземпляров книг, указать это количество. 
Последний столбец назвать Количество.
*/

select name_genre, sum(buy_book.amount) as Количество
from genre 

    inner join book using(genre_id)
    inner join buy_book using(book_id)
group by 1
limit 1
;
--------------------------------------------------------------------------------------------------------------------------
/*
Сравнить ежемесячную выручку от продажи книг за текущий и предыдущий годы. Для этого вывести год, месяц, сумму выручки 
в отсортированном сначала по возрастанию месяцев, затем по возрастанию лет виде. Название столбцов: Год, Месяц, Сумма.
*/

select year(date_payment) as Год, monthname(date_payment) as Месяц, sum(amount*price) as Сумма
from 
    buy_archive
group by 1, 2
union all

select year(date_step_end) as Год, monthname(date_step_end) as Месяц, sum(buy_book.amount*book.price) as Сумма
FROM 
    book 
    INNER JOIN buy_book USING(book_id)
    INNER JOIN buy USING(buy_id)
    INNER JOIN buy_step USING(buy_id)
    INNER JOIN step USING(step_id)
WHERE  date_step_end IS NOT Null and name_step = "Оплата"
group by 1, 2
order by 2, 1
;
-------------------------------------------------------------------------------------------------------------------------
/*
Для каждой отдельной книги необходимо вывести информацию о количестве проданных экземпляров и их стоимости за 2020 и 
2019 год . Вычисляемые столбцы назвать Количество и Сумма. Информацию отсортировать по убыванию стоимости.
*/

select title, sum(amount) as Количество, sum(price) as Сумма

    from (
    select title, buy_archive.amount, buy_archive.price * buy_archive.amount as price
    from buy_archive 
    inner join book using(book_id)

union all

select title, buy_book.amount , buy_book.amount*book.price
from 
    book
    inner join buy_book using(book_id)
    inner join buy using(buy_id)
    inner join buy_step using(buy_id)
    inner join step using(step_id)
    where name_step = "Оплата" and buy_step.date_step_end is not null
        ) as query_in
group by 1
order by 3 desc
;
------------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов на выборку для предметной области «Интернет-магазин книг»
*/

select title, name_genre, name_author, name_client, name_city, datediff(date_step_end, date_step_beg) as Количество_дней, greatest(datediff(date_step_end, date_step_beg) - days_delivery, 0) as Опоздание
from 
    book
    inner join genre using(genre_id)
    inner join author using(author_id)
    inner join buy_book using(book_id)
    inner join buy using(buy_id)
    inner join client using(client_id)
    inner join city using(city_id)
    inner join buy_step using(buy_id)
    inner join step using(step_id)
where datediff(date_step_end, date_step_beg) > 0 and step_id = 3
order by 6
;
-----------------------------------------------------------------------------------------------------------------------
/*
Включить нового человека в таблицу с клиентами. Его имя Попов Илья, его email popov@test, проживает он в Москве.
*/

insert into client(name_client, city_id, email)
select 'Попов Илья', city_id, 'popov@test'
from city
where name_city = 'Москва'
;
select * from client;
-----------------------------------------------------------------------------------------------------------------------
/*
Создать новый заказ для Попова Ильи. Его комментарий для заказа: «Связаться со мной по вопросу доставки».
*/

insert into buy(buy_description, client_id)
select 'Связаться со мной по вопросу доставки', client_id
from client
where name_client = 'Попов Илья'
;

select * from buy;
-----------------------------------------------------------------------------------------------------------------------
/*
В таблицу buy_book добавить заказ с номером 5. Этот заказ должен содержать книгу Пастернака «Лирика» в количестве 
двух экземпляров и книгу Булгакова «Белая гвардия» в одном экземпляре.
*/

insert into buy_book (buy_id, book_id, amount)
select 5, book_id, 2
from buy_book 
    inner join book using(book_id)
    inner join author using(author_id)
where title='Лирика' and name_author like 'Пастернак%'
;


insert into buy_book (buy_id, book_id, amount)
select 5, book_id, 1
from buy_book 
    inner join book using(book_id)
    inner join author using(author_id)
where title='Белая Гвардия' and name_author like 'Булгаков%'
;

select * from buy_book;

------------------------------------------------------------------------------------------------------------------------
/*
Количество тех книг на складе, которые были включены в заказ с номером 5, уменьшить на то количество, 
которое в заказе с номером 5  указано.
*/

update buy_book, book
set book.amount = book.amount - buy_book.amount
where buy_book.buy_id = 5 and book.book_id = buy_book.book_id
;

select * from book;
------------------------------------------------------------------------------------------------------------------------
/*
Создать счет (таблицу buy_pay) на оплату заказа с номером 5, в который включить название книг, их автора, цену, 
количество заказанных книг и  стоимость. Последний столбец назвать Стоимость. Информацию в таблицу занести в 
отсортированном по названиям книг виде.
*/

create table buy_pay as
select book.title, author.name_author, book.price, buy_book.amount, book.price*buy_book.amount as 'Стоимость'
from buy_book 
    inner join book using(book_id)
    inner join author using(author_id)
where buy_id = 5
order by 1
;

select * from buy_pay;

-----------------------------------------------------------------------------------------------------------------------
/*
Создать общий счет (таблицу buy_pay) на оплату заказа с номером 5. Куда включить номер заказа, количество книг в заказе 
(название столбца Количество) и его общую стоимость (название столбца Итого). Для решения используйте ОДИН запрос.
*/

create table buy_pay as
select buy_id, sum(buy_book.amount) as Количество, sum(book.price*buy_book.amount) as Итого
from buy_book 
    inner join book using(book_id)
    inner join author using(author_id)
where buy_id = 5
order by 1
;

select * from buy_pay;
-----------------------------------------------------------------------------------------------------------------------
/*
В таблицу buy_step для заказа с номером 5 включить все этапы из таблицы step, которые должен пройти этот заказ. 
В столбцы date_step_beg и date_step_end всех записей занести Null.
*/

insert into buy_step(buy_id, step_id, date_step_beg, date_step_end)
select buy_id, step_id, null, null
from buy
cross join step
where buy_id = 5
;
------------------------------------------------------------------------------------------------------------------------
/*
В таблицу buy_step занести дату 12.04.2020 выставления счета на оплату заказа с номером 5.

Правильнее было бы занести не конкретную, а текущую дату. Это можно сделать с помощью функции Now(). 
Но при этом в разные дни будут вставляться разная дата, и задание нельзя будет проверить, поэтому  вставим дату 12.04.2020.
*/
update buy_step
set date_step_beg = '2020-04-12'
where buy_id = 5 and step_id = 1
;

select * from buy_step;
-------------------------------------------------------------------------------------------------------------------------
/*
Завершить этап «Оплата» для заказа с номером 5, вставив в столбец date_step_end дату 13.04.2020, и начать следующий этап 
(«Упаковка»), задав в столбце date_step_beg для этого этапа ту же дату.

Реализовать два запроса для завершения этапа и начала следующего. Они должны быть записаны в общем виде, чтобы его можно 
было применять для любых этапов, изменив только текущий этап. Для примера пусть это будет этап «Оплата».
*/

update buy_step
set date_step_end = '2020-04-13'
where buy_id = 5 and step_id = 1
;

update buy_step 
set date_step_beg = '2020-04-13'
where buy_id = 5 and step_id = (select step_id + 1 from step
where name_step = 'Оплата')
;      

select * from buy_step;
---------------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов корректировки данных для предметной области «Интернет-магазин книг» 
(в таблицы занесены данные, как на этом шаге). Проверьте, правильно ли они работают.
*/

select buy_book.buy_book_id as 'Номер заказа',
        client.name_client as 'Клиент',
        buy_book.amount * book.price as 'Сумма заказа',
        buy_step.date_step_beg as 'Заказ от',
        step.name_step as 'Этап',
        city.name_city as 'Город'
from 
    book
    inner join genre using(genre_id)
    inner join author using(author_id)
    inner join buy_book using(book_id)
    inner join buy using(buy_id)
    inner join client using(client_id)
    inner join city using(city_id)
    inner join buy_step using(buy_id)
    inner join step using(step_id)

where name_step = 'Доставка'

order by 2
;

--------------------------------------------------------------------------------------------------------------------------
/*
Вывести студентов, которые сдавали дисциплину «Основы баз данных», указать дату попытки и результат. Информацию 
вывести по убыванию результатов тестирования.
*/

select student.name_student, attempt.date_attempt, attempt.result
from attempt
    inner join student using(student_id)
    inner join subject using(subject_id)
where name_subject = 'Основы баз данных'
order by 3 desc
;
--------------------------------------------------------------------------------------------------------------------------
/*
Вывести, сколько попыток сделали студенты по каждой дисциплине, а также средний результат попыток, который округлить 
до 2 знаков после запятой. Под результатом попытки понимается процент правильных ответов на вопросы теста, который 
занесен в столбец result.  В результат включить название дисциплины, а также вычисляемые столбцы Количество и Среднее. 
Информацию вывести по убыванию средних результатов.
*/

select name_subject, count(attempt_id) as Количество, round(avg(result), 2) as Среднее
from subject
    left join attempt using(subject_id)
group by 1
order by 3 desc
;
--------------------------------------------------------------------------------------------------------------------------
/*
Вывести студентов (различных студентов), имеющих максимальные результаты попыток. Информацию отсортировать в алфавитном 
порядке по фамилии студента.
Максимальный результат не обязательно будет 100%, поэтому явно это значение в запросе не задавать.
*/

select name_student, result
from student
    inner join attempt using(student_id)
where result = (select max(result) from attempt)

order by 1
;
--------------------------------------------------------------------------------------------------------------------------
/*
Если студент совершал несколько попыток по одной и той же дисциплине, то вывести разницу в днях между первой и последней 
попыткой. В результат включить фамилию и имя студента, название дисциплины и вычисляемый столбец Интервал. Информацию 
вывести по возрастанию разницы. Студентов, сделавших одну попытку по дисциплине, не учитывать. 
*/

select name_student, name_subject, datediff(max(date_attempt), min(date_attempt)) as Интервал
from attempt
    inner join student using(student_id)
    inner join subject using(subject_id)
group by 1, 2
having count(attempt_id) > 1 
order by 3
;

--------------------------------------------------------------------------------------------------------------------------
/*
Студенты могут тестироваться по одной или нескольким дисциплинам (не обязательно по всем). Вывести дисциплину и количество 
уникальных студентов (столбец назвать Количество), которые по ней проходили тестирование . Информацию отсортировать сначала 
по убыванию количества, а потом по названию дисциплины. В результат включить и дисциплины, тестирование по которым студенты 
еще не проходили, в этом случае указать количество студентов 0.
*/

select name_subject, count(distinct student_id) as Количество
from subject
    left join attempt using(subject_id)
group by 1
order by 2 desc
;
---------------------------------------------------------------------------------------------------------------------------
/*
Случайным образом отберите 3 вопроса по дисциплине «Основы баз данных». В результат включите столбцы question_id 
и name_question.
*/

select question_id, name_question
from subject 
    inner join question using(subject_id)
where subject_id = 2
order by rand()
limit 3
;
----------------------------------------------------------------------------------------------------------------------------
/*
Вывести вопросы, которые были включены в тест для Семенова Ивана по дисциплине «Основы SQL» 2020-05-17  
(значение attempt_id для этой попытки равно 7). Указать, какой ответ дал студент и правильный он или нет 
(вывести Верно или Неверно). В результат включить вопрос, ответ и вычисляемый столбец  Результат.
*/

select name_question, name_answer, if(is_correct, 'Верно', 'Неверно') as Результат
from question
    inner join testing using(question_id)
    inner join answer using(answer_id)
where attempt_id = 7
;
----------------------------------------------------------------------------------------------------------------------------
/*
Посчитать результаты тестирования. Результат попытки вычислить как количество правильных ответов, деленное на 3 
(количество вопросов в каждой попытке) и умноженное на 100. Результат округлить до двух знаков после запятой. 
Вывести фамилию студента, название предмета, дату и результат. Последний столбец назвать Результат. 
Информацию отсортировать сначала по фамилии студента, потом по убыванию даты попытки.
*/

select name_student, name_subject, date_attempt, round(sum((answer.is_correct) * 100 / 3), 2) as Результат
from answer
    inner join testing using(answer_id)
    inner join attempt using(attempt_id)
    inner join subject using(subject_id)
    inner join student using(student_id)
group by 1, 2, 3
order by 1, 3 desc
;
----------------------------------------------------------------------------------------------------------------------------
/*
Для каждого вопроса вывести процент успешных решений, то есть отношение количества верных ответов к общему количеству 
ответов, значение округлить до 2-х знаков после запятой. Также вывести название предмета, к которому относится вопрос, 
и общее количество ответов на этот вопрос. В результат включить название дисциплины, вопросы по ней (столбец назвать Вопрос), 
а также два вычисляемых столбца Всего_ответов и Успешность. Информацию отсортировать сначала по названию дисциплины, потом 
по убыванию успешности, а потом по тексту вопроса в алфавитном порядке.

Поскольку тексты вопросов могут быть длинными, обрезать их 30 символов и добавить многоточие "...".
*/

select name_subject,
        concat(left(name_question, 30), '...') as Вопрос,
        count(answer_id) as Всего_ответов,
        round((sum(is_correct) / count(answer_id)) * 100, 2) as Успешность
from testing
    inner join question using (question_id)
    inner join answer using (answer_id)
    left join subject using (subject_id) 
group by 1, 2 
order by 1, 4 desc, 2
;
----------------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов на выборку для предметной области «Тестирование» (в таблицы занесены данные, как 
на первом шаге урока). Проверьте, правильно ли они работают.
*/

select student.name_student as 'Студент',
    subject.name_subject as 'Курс',
    attempt.date_attempt as 'Дата_попытки',
    attempt.result as 'MAX_Оценка'
from attempt
    inner join student using(student_id)
    inner join subject using(subject_id)
where result = (select max(result) from attempt)
order by 1
;

select student.name_student as 'Студент',
    concat(left(answer.name_answer, 20), '...') as 'Ответ',
    answer.question_id as 'Вопрос_id',
    answer.is_correct as 'Оценка'
    
from student
    inner join attempt using (student_id)
    inner join testing using (attempt_id)
    inner join answer using (answer_id) 
where answer.is_correct = 1
group by 1, 2, 3
order by 1
;

select avg(attempt.result) as 'Средняя_оценка'
from attempt
;
-----------------------------------------------------------------------------------------------------------------------------

/*
В таблицу attempt включить новую попытку для студента Баранова Павла по дисциплине «Основы баз данных». Установить текущую 
дату в качестве даты выполнения попытки.
*/

insert into attempt(student_id, subject_id, date_attempt, result)
select student.student_id, subject.subject_id, now(), null
from student, subject
where name_student = 'Баранов Павел'and name_subject = 'Основы баз данных'
;

select * from attempt
;
-----------------------------------------------------------------------------------------------------------------------------
/*
Случайным образом выбрать три вопроса (запрос) по дисциплине, тестирование по которой собирается проходить студент, 
занесенный в таблицу attempt последним, и добавить их в таблицу testing. id последней попытки получить как максимальное 
значение id из таблицы attempt.
*/

insert into testing(attempt_id, question_id)
select attempt_id, question_id
from question 
    inner join attempt using(subject_id)
where attempt_id = (select max(attempt_id) from attempt)
order by rand()
limit 3
;
select * from testing;
------------------------------------------------------------------------------------------------------------------------------
/*
Студент прошел тестирование (то есть все его ответы занесены в таблицу testing), далее необходимо вычислить результат(запрос) 
и занести его в таблицу attempt для соответствующей попытки.  Результат попытки вычислить как количество правильных ответов, 
деленное на 3 (количество вопросов в каждой попытке) и умноженное на 100. Результат округлить до целого.

 Будем считать, что мы знаем id попытки,  для которой вычисляется результат, в нашем случае это 8. В таблицу testing занесены 
 следующие ответы пользователя:
*/

update attempt 
set result = (select round((sum(is_correct)/3) * 100, 2)
            from answer
            inner join testing on answer.answer_id= testing.answer_id
            where testing.attempt_id = 8)
where attempt.attempt_id = 8
;
select * from attempt;
------------------------------------------------------------------------------------------------------------------------------
/*
Удалить из таблицы attempt все попытки, выполненные раньше 1 мая 2020 года. Также удалить и все соответствующие этим 
попыткам вопросы из таблицы testing
*/

delete from attempt 
where date_attempt < '2020-05-01'
;
select * from attempt;
select * from testing;
------------------------------------------------------------------------------------------------------------------------------
/*
Придумайте один или несколько запросов корректировки данных для предметной области «Тестирование» (в таблицы занесены данные, 
как на первом шаге урока). Проверьте, правильно ли они работают.
*/
delete from answer
where is_correct = '0'
;
select * from answer;


insert into attempt(subject_id, student_id, date_attempt)
select subject_id, student_id, now()
from attempt
    right join subject using(subject_id)
    right join student using(student_id)
   
;

select * from attempt
-- where result is null 
;


select attempt.student_id, student.name_student, name_subject, attempt.subject_id, attempt.date_attempt, attempt.result
from attempt
    left join subject using(subject_id)
    cross join student using(student_id)
order by 2, 5

-- where result in (select result < 60 from attempt)
;
------------------------------------------------------------------------------------------------------------------------------
/*
Вывести абитуриентов, которые хотят поступать на образовательную программу «Мехатроника и робототехника» в отсортированном 
по фамилиям виде.
*/

select name_enrollee
from enrollee
inner join program_enrollee using(enrollee_id)
inner join program using(program_id)
where name_program = 'Мехатроника и робототехника'
order by 1 
;
------------------------------------------------------------------------------------------------------------------------------
/*
Вывести образовательные программы, на которые для поступления необходим предмет «Информатика». Программы отсортировать 
в обратном алфавитном порядке.
*/

select name_program
from program
inner join program_subject using(program_id)
inner join subject using(subject_id)
where name_subject = 'Информатика'
;
------------------------------------------------------------------------------------------------------------------------------
/*
Выведите количество абитуриентов, сдавших ЕГЭ по каждому предмету, максимальное, минимальное и среднее значение баллов по 
предмету ЕГЭ. Вычисляемые столбцы назвать Количество, Максимум, Минимум, Среднее. Информацию отсортировать по названию предмета 
в алфавитном порядке, среднее значение округлить до одного знака после запятой.
*/

select  name_subject, 
        count(enrollee_id) as Количество,
        max(result) as Максимум,
        min(result) as Минимум,
        round(avg(result), 1) as Среднее
from subject
inner join enrollee_subject using(subject_id)
group by 1 
order by 1
;
------------------------------------------------------------------------------------------------------------------------------
/*
Вывести образовательные программы, для которых минимальный балл ЕГЭ по каждому предмету больше или равен 40 баллам. 
Программы вывести в отсортированном по алфавиту виде.
*/

select name_program
from program
inner join program_subject using(program_id)
-- where min_result >= 40
group by 1 
having min(min_result) >= 40
order by 1
;
------------------------------------------------------------------------------------------------------------------------------
/*
Вывести образовательные программы, которые имеют самый большой план набора,  вместе с этой величиной.
*/
select name_program, plan
from program
where (select max(plan) from program)
order by 2 desc
limit 1
;
------------------------------------------------------------------------------------------------------------------------------
/*
Посчитать, сколько дополнительных баллов получит каждый абитуриент. Столбец с дополнительными баллами назвать Бонус. 
Информацию вывести в отсортированном по фамилиям виде
*/
select name_enrollee, 
        if (sum(bonus) is null, 0, sum(bonus)) as Бонус
from achievement
right join enrollee_achievement using(achievement_id)
right join enrollee using(enrollee_id)
group by 1
order by 1
;
------------------------------------------------------------------------------------------------------------------------------
/*
Выведите сколько человек подало заявление на каждую образовательную программу и конкурс на нее (число поданных заявлений 
деленное на количество мест по плану), округленный до 2-х знаков после запятой. В запросе вывести название факультета, 
к которому относится образовательная программа, название образовательной программы, план набора абитуриентов на образовательную 
программу (plan), количество поданных заявлений (Количество) и Конкурс. Информацию отсортировать в порядке убывания конкурса.
*/
select name_department, 
        name_program, 
        plan, 
        count(name_program) as Количество, 
        round(count(name_program) / plan,2) as Конкурс
from department
inner join program using(department_id)
inner join program_enrollee using(program_id)
group by 1, 2, 3
order by 5 desc
;
------------------------------------------------------------------------------------------------------------------------------
/*
Вывести образовательные программы, на которые для поступления необходимы предмет «Информатика» и «Математика» в 
отсортированном по названию программ виде.
*/
select name_program
from subject
inner join program_subject using(subject_id)
inner join program using(program_id)
where name_subject  = 'Информатика' or 'Математика'
order by 1
;
------------------------------------------------------------------------------------------------------------------------------