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

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

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

Магазин счёл, что классика уже не пользуется популярностью, поэтому необходимо в выборке:

1. Сменить всех авторов на «Донцова Дарья».
2. К названию каждой книги в начале дописать «Евлампия Романова и » ( пробел в конце).
3. Цену поднять на 42% (округлить её до двух знаков после запятой).
4. Отсортировать по убыванию цены.

SELECT 'Донцова Дарья' AS author,
     CONCAT('Евлампия Романова и ', title) AS title,
     ROUND(price*1.42, 2) AS price 
FROM book
ORDER BY 3 DESC

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

При реализации в основном запросе не используйте LIMIT, поскольку жанров с минимальным количеством заказанных книг может быть несколько.

WITH tab(name_genre, Количество) AS
    (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 name_genre
    )
SELECT name_genre, Количество
FROM tab
WHERE Количество = (SELECT MIN(Количество) FROM tab)

Создать новую таблицу store, в которую занести данные из таблиц book и supply, при условии, что количество книг будет больше среднего количества книг по двум таблицам; если книга есть в обеих таблицах, то стоимость выбрать большую из двух. Отсортировать данные из таблицы их по имени автора в алфавитном порядке и по убыванию цены. Вывести данные из полученной таблицы.

CREATE TABLE store AS
with t1 AS
    (SELECT title, author, price, amount
    FROM book
    UNION ALL
    SELECT title, author, price, amount
    FROM supply
    )
    
SELECT title, 
    author, 
    MAX(price) AS price, 
    sum(amount) AS amount
FROM t1
GROUP BY title, author
HAVING amount > (SELECT AVG(amount) FROM t1)
ORDER BY author, price DESC;

SELECT * 
FROM store

Объявить столбец «категории цены» (price_category): <500 — «низкая», 500 — 700 — «средняя», более 700 — «высокая»
Вывести автора, название, категорию, стоимость (цена * количество), исключив из авторов Есенина, из названий «Белую гвардию». Отсортировать по убыванию стоимости и названию (по возрастанию)

SELECT author, title,
    CASE
        WHEN price < 500 THEN 'низкая'
        WHEN price BETWEEN 500 AND 700 THEN 'средняя'
        ELSE 'высокая'
        END AS price_category, price * amount AS cost
FROM book
WHERE author <> 'Есенин С.А.' AND title <> 'Белая гвардия'
ORDER BY cost DESC, title

Для нечетного количества книг посчитать разницу максимальной стоимости (цена * количество) и стоимостью всех экземпляров конкретной книги. Отсортировать по этой разнице по убыванию. Вывести название, автора, количество, разницу с максимальной стоимостью.

SELECT title, 
    author, 
    amount, 
    (SELECT price * amount
         FROM book
         WHERE amount % 2 = 1
         ORDER BY price * amount DESC
         LIMIT 1) - amount * price AS Разница_с_макс_стоимостью
FROM book
WHERE amount % 2 = 1
ORDER BY Разница_с_макс_стоимостью DESC

Магазин решил быстрее распродать остатки книг, цена которых выше 600, а также прописать условия доставки. Создать запрос на выборку, в котором:

1. Столбцы назовите Наименование, Цена и  Стоимость доставки.
2. Отберите все книги, цена которых выше 600.
3. Если остаток по отдельной книге меньше или равен 5, то стоимость доставки будет 500 рублей, если больше 5, то доставка будет бесплатной (вместо стоимости доставки вставить Бесплатно).
4. Отсортируйте значения по убыванию цены книг.

SELECT title AS Наименование, 
    price AS Цена,  
    CASE 
        WHEN amount <= 5 THEN '500'
    ELSE 'Бесплатно'
    END AS Стоимость_доставки
FROM book
WHERE price > 600
ORDER BY price DESC

На распродаже размер скидки устанавливается в зависимости от количества экземпляров книги в магазине и от цены книги: для книг в остатке не менее 5 шт скидка 50%, тогда как для книг в остатке менее 5 шт скидка устанавливается в зависимости от цены (на книги не дешевле 700 руб скидка 20%, на остальные 10%). Два последних столбца назвать Скидка и Цена_со_скидкой.  Последний столбец округлить до двух знаков после запятой.

SELECT author, 
    title, 
    amount, 
    price,
    CASE
        WHEN amount >= 5 THEN '50%'
        WHEN price >= 700 THEN '20%'
        ELSE '10%' 
    END AS Скидка, 
    CASE
        WHEN amount >= 5 THEN ROUND(price * 0.5, 2)
        WHEN price >= 700 THEN ROUND(price * 0.8, 2)
        ELSE ROUND(price * 0.9, 2)
    END AS Цена_со_скидкой
FROM 
    book 

Определить стоимость доставки:
— для книг c ценой 500 и менее, установить в размере 99.99
— при количестве книг на складе менее 5, а ценой выше 500, установить в размере 149.99
— для остальных случаев доставка должна быть бесплатной
Определить новую стоимость для книг:
— для книг, совокупной стоимостью более 5000, добавить 20% к стоимости за экземпляр
— для остальных случаев снизить стоимость одного экземпляра на 20%
Настроить фильтр при выборке:
— только позиции творчества авторов: Булгаков и Есенин, при количестве экземпляров на складе: от 3 до 14 включительно.
Сортировку выполнить:
— по имени автора в порядке возрастания
— затем по названию в порядке убывания
— по стоимости доставки (от меньшей к большей)
В таблице должны быть отображены данные:
— автора
— название
— количество
— цену, как real_price
— новую цену, как new_price (округлить до двух знаков после запятой)
— стоимость доставки, как delivery_price 

SELECT author, 
    title, 
    amount, 
    price AS real_price, 
    ROUND(IF(price * amount > 5000, price * 1.2, price * 0.8), 2) AS new_price, 
    ROUND(IF(price <= 500, 99.99, IF(amount < 5, 149.99, 0.00)), 2) AS delivery_price 
FROM 
    book 
WHERE 
    author IN ('Булгаков М.А.', 'Есенин С.А.') AND amount BETWEEN 3 AND 14 
ORDER BY author, title DESC, delivery_price