Разобрали ответы и решения урока 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, а также прописать условия доставки. Создать запрос на выборку, в котором:
- Столбцы назовите
Наименование
,Цена
иСтоимость доставки
.- Отберите все книги, цена которых выше 600.
- Если остаток по отдельной книге меньше или равен 5, то стоимость доставки будет 500 рублей, если больше 5, то доставка будет бесплатной (вместо стоимости доставки вставить Бесплатно).
- Отсортируйте значения по убыванию цены книг.
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