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

Разобрали ответы и решения урока 2.3(Запросы корректировки, соединение таблиц) под курс «Интерактивный тренажер по SQL».

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

Для книг, которые уже есть на складе (в таблице book), но по другой цене, чем в поставке (supply), необходимо в таблице book увеличить количество на значение, указанное в поставке, и пересчитать цену. А в таблице supply обнулить количество этих книг. Формула для пересчета цены:
где p1, p2 — цена книги в таблицах book и supply;
k1, k2 — количество книг в таблицах book и supply.

UPDATE book b
        INNER JOIN author a USING(author_id)
        INNER JOIN supply s ON b.title=s.title 
                                AND a.name_author=s.author
SET b.amount=b.amount + s.amount,
    b.price=(b.price*b.amount + s.price*s.amount)/(b.amount+s.amount),
    s.amount=0
WHERE b.price <> s.price;

Включить новых авторов в таблицу author с помощью запроса на добавление, а затем вывести все данные из таблицы author. Новыми считаются авторы, которые есть в таблице supply, но нет в таблице author

INSERT INTO author (name_author)
SELECT supply.author
FROM 
    author 
    RIGHT JOIN supply on author.name_author = supply.author
WHERE 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 amount <> 0;

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

UPDATE book, genre
SET book.genre_id =
    CASE
        WHEN book_id = 10 THEN (SELECT genre_id FROM genre WHERE name_genre = 'Поэзия')
        WHEN book_id = 11 THEN (SELECT genre_id FROM genre WHERE name_genre = 'Приключения')
        ELSE book.genre_id
    END;
SELECT * FROM book;

Удалить всех авторов и все их книги, общее количество книг которых меньше 20.

DELETE FROM author
WHERE author_id IN
    (
    SELECT author_id
    FROM book
    GROUP BY author_id
    HAVING SUM(amount) < 20
    );

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

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

Удалить всех авторов, которые пишут в жанре «Поэзия». Из таблицы book удалить все книги этих авторов. В запросе для отбора авторов использовать полное название жанра, а не его id.

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

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

DELETE FROM author
WHERE author_id IN (SELECT author_id
                    FROM book
                    WHERE genre_id = (SELECT genre_id
                                     FROM book
                                     GROUP BY genre_id
                                     HAVING SUM(amount)
                                     ORDER BY SUM(amount) DESC
                                     LIMIT 1) 
                    GROUP BY author_id);