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

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

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

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

SELECT beg_range, end_range,
     ROUND(AVG(price), 2) AS Средняя_цена,
     SUM(price * amount) AS Стоимость,
     COUNT(amount) AS Количество
FROM(
    SELECT beg_range, end_range, price, amount
    FROM stat 
    JOIN book ON beg_range<price AND end_range>price
    ) table1    
GROUP BY beg_range, end_range
ORDER BY 1

Вывести всю информацию из таблицы book, упорядоченную по возрастанию длины названия книги.

SELECT *
FROM book
ORDER BY LENGTH(title)

Удалить из таблиц book и supply книги, цены которых заканчиваются на 99 копеек. Например, книга с ценой 670.99 должна быть удалена.

DELETE book, supply 
FROM book, supply
WHERE book.price LIKE '%.99' 
    AND supply.price LIKE '%.99';

Снизить цены книг, цена которых больше 600 рублей, на 20%. Вывести информацию о книгах, скидку (столбец sale_20) и цену книги со скидкой (price_sale).  Результаты округлить до двух знаков после запятой. Для тех книг, на которые скидка не действует, в последних двух столбцах вывести символ  «-«.  Отсортировать информацию сначала по фамилии автора, а потом по названию книги.

SELECT author, title, price, amount,
    IF(price > 600, ROUND(price * 0.2, 2), '-') AS sale_20, 
    IF(price > 600, ROUND(price * 0.8, 2),  '-') AS price_sale
FROM book
ORDER BY author, title

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

SET @avg_price := (SELECT AVG(price) FROM book);

SELECT author,  
    SUM(price * amount) AS Стоимость 
FROM book
WHERE author in (SELECT author FROM book WHERE price > @avg_price)
GROUP BY author
ORDER BY 2 DESC;

Вывести автора, название, количество, цену (Розничная_цена). Для тех книг количество которых больше или равно 10, отобразить оптовую скидку 15% (Скидка), округлить до двух знаков после запятой и вывести оптовую цену с учетом скидки -15% (Оптовая_цена). Все атрибуты перевести на русский язык. Отсортировать по автору и названию книги.

SELECT author AS "Автор", title AS "Название_книги", amount AS "Количество", price AS "Розничная_цена",
    IF (amount >= 10, 15, 0) AS "Скидка",
    round (IF (amount >= 10, price * 0.85, price), 2) AS "Оптовая_цена"    
FROM book
ORDER BY author, title;

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

SELECT author, 
    COUNT(author) AS Количество_произведений, 
    MIN(price) AS Минимальная_цена, 
    SUM(amount) AS Число_книг 
FROM book
WHERE amount > 1
GROUP BY author
HAVING COUNT(author) > 1
ORDER BY author;