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

Разобрали ответы и решения урока 4.3(База данных «Интернет-магазин книг», часть 3) под курс «Интерактивный тренажер по SQL».

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

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

SELECT author, title, 
    LEFT(price, 3) AS Рубли, 
    RIGHT(CEIl(RIGHT(price, 2)), 2) AS Копейки
FROM 
    book
ORDER BY Копейки DESC

В связи с повышенным спросом на классическую литературу школьниками в формате «А есть то же самое, но покороче, чтобы читать поменьше?» была выпущена серия «Графоман и. Краткое содержание».
В выборке:
— к имени автора добавить «Графоман и «;
— к названию книги дописать «. Краткое содержание.»;
— цену на новый опус установить 40% от цены оригинала, но не более 250. (Если 40% больше 250, то цена должна быть 250);
— в зависимости от остатка на складе вывести «Спрос»: до 3 (включительно) — высокий, до 10 (включительно) — средний, иначе низкий;
— добавить колонку «Наличие» в зависимости от количества: 1-2 шт — очень мало, 3-14 — в наличии, 15 и больше — много;
— отсортировать по цене по возрастанию, затем по Спросу от высокого к низкому, а затем по названию книги в алфавитном порядке.

Структура таблицы:

SELECT CONCAT('Графоман и ',author) AS Автор,
    CONCAT(title,'. Краткое содержание.') AS Название,
    IF(price*0.4 > 250,250,round(price*0.4,3)) AS Цена,
    IF(amount <= 3,'высокий',if(amount <= 10,'средний','низкий')) AS Спрос,
    IF(amount <= 2,'очень мало',if(amount <= 14,'в наличии','много')) AS Наличие
FROM 
    book
ORDER BY 3,amount,2

Для клиентов у которых сумма заказов выше средней по суммам заказов клиентов (общей стоимости всех заказов клиентов), вывести имя, общую сумму всех заказов, количество заказов, количество заказанных книг. Этим клиентам мы предложим специальную программу лояльности! Информацию отсортировать по имени клиентов ( в алфавитном порядке).

SELECT name_client, SUM(price * bb.amount) AS Общая_сумма_заказов, COUNT(DISTINCT bb.buy_id) AS Заказов_всего,         SUM(bb.amount) AS Книг_всего
FROM book
    JOIN buy_book bb USING(book_id)
    JOIN buy USING(buy_id)
    JOIN client USING(client_id)
GROUP BY name_client
HAVING SUM(price * bb.amount) > (SELECT AVG(pr)
                                 FROM (SELECT SUM(price * buy_book.amount) AS pr
                                       FROM book
                                           JOIN buy_book USING(book_id)
                                           JOIN buy USING(buy_id)
                                       GROUP BY buy.client_id) test_1
                                 )
ORDER BY name_client

Составить рейтинг книг в зависимости от того, какая книга принесет больше всего выручки (в процентах), при условии продажи всех книг. Рейтинг отсортировать по убыванию выручки. Выручка в процентах вычисляется как стоимость всех экземпляров книги деленное на суммарную стоимость всех экземпляров книг на складе и умноженное на 100, полученный результат округлить до двух знаков после запятой.
Судя по результату, магазин хорошо вложился в Стихи Есенина

SET @c := (SELECT SUM(t) 
           FROM 
               (SELECT SUM(price*amount) AS t 
                    FROM book) AS t1);

SELECT author, 
    title, 
    price, 
    amount, 
    ROUND(((price*amount) / @c) * 100,2) AS income_percent
FROM 
    book
ORDER BY income_percent DESC;

Для каждого автора из таблицы author вывести количество книг, написанных им в каждом жанре.

Вывести: ФИО автора, жанр, количество.

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

Важно! Реализовать задание одним запросом на выборку.

SELECT name_author, 
    name_genre, 
    COUNT(title) AS Количество
FROM author 
    CROSS JOIN genre 
    LEFT JOIN book ON book.author_id = author.author_id AND genre.genre_id = book.genre_id
GROUP BY 
    name_author, name_genre
ORDER BY 
    name_author, Количество DESC, name_genre

Акция «Купи книгу от 500 руб. и получи подарок».
Вывести автора, название книги и цену. Выбрать книги с ценой 500 рублей и выше, отсортировать информацию в алфавитном порядке по автору и названию книги. Добавить столбец Подарок,  в котором вывести, какой подарок получает покупатель: если куплена книга от 500 рублей до 600 рублей (включительно), то подарок — ручка, от 600 до 700 (включительно) — детская раскраска, выше 700 — гороскоп.

SELECT author AS Автор,
       title AS Название_книги,
       price AS Цена,
       CASE WHEN price>500 AND price<=600 THEN 'ручка'
            WHEN price>600 AND price<=700 THEN 'детская раскраска'
            WHEN price>700 THEN 'гороскоп'
       end AS Подарок
FROM 
    book
WHERE 
    price >=500
ORDER BY 
    Автор, Название_книги;

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

SELECT author AS Автор,
    MIN(amount) AS Наименьшее_кол_во,
    MAX(amount) AS Наибольшее_кол_во 
FROM 
    book 
GROUP BY
    author
HAVING SUM(amount) < 10;

В последний заказ (таблица buy_book) клиента Баранов Павел добавить по одному экземпляру всех книг Достоевского, которые есть в таблице book.

set @m:= (SELECT MAX(buy_id)
          FROM buy_book 
              JOIN buy using(buy_id) 
              JOIN client using(client_id)
          WHERE name_client = 'Баранов Павел');
          
INSERT INTO buy_book(buy_id, book_id, amount)
SELECT @m AS buy_id, 
    book_id,1 AS amount
FROM 
    author 
    JOIN book using(author_id)
WHERE name_author like 'Достоевский%';