Для книг, которые уже есть на складе (в таблице 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);