Разобрали ответы и решения урока 1.5(Запросы корректировки данных) под курс «Интерактивный тренажер по SQL».
Предыдущий разбор модуля 1.4 следующий разбор модуль 1.6
Создать таблицу поставка (
supply
), которая имеет ту же структуру, что и таблицbook
.
CREATE TABLE supply (
supply_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50),
author VARCHAR(30),
price DECIMAL(8, 2),
amount INT
);
Занесите в таблицу
supply
четыре записи, чтобы получилась следующая таблица:
INSERT INTO supply(title, author, price, amount)
VALUES
('Лирика','Пастернак Б.Л.',518.99,2),
('Черный человек','Есенин С.А.',570.20,6),
('Белая гвардия','Булгаков М.А.',540.50,7),
('Идиот','Достоевский Ф.М.',360.80,3);
SELECT *
FROM supply;
Пояснение: supply_id можно не указывать так как это поле AUTO_INCREMENT
Занести все книги из таблицы
supply
в таблицуbook
.
INSERT INTO book (title, author, price, amount)
SELECT title, author, price, amount
FROM supply
WHERE author <> 'Булгаков М.А.'
AND author <> 'Достоевский Ф.М.';
Занести из таблицы
supply
в таблицуbook
только те книги, авторов которых нет вbook.
INSERT INTO book (title, author, price, amount)
SELECT title, author, price, amount
FROM supply
WHERE author NOT IN (
SELECT author
FROM book
);
Уменьшить на 10% цену тех книг в таблице
book
, количество которых принадлежит интервалу от 5 до 10, включая границы.
UPDATE book
SET price = price * 0.9
WHERE amount BETWEEN 5 AND 10;
В таблице book необходимо скорректировать значение для покупателя в столбце buy таким образом, чтобы оно не превышало количество экземпляров книг, указанных в столбце amount. А цену тех книг, которые покупатель не заказывал, снизить на 10%.
UPDATE book
SET buy = IF(buy > amount, amount, buy),
price = IF(buy = 0 , price * 0.9, price);
SELECT * FROM book;
Для тех книг в таблице
book
, которые есть в таблицеsupply,
не только увеличить их количество в таблицеbook
( увеличить их количество на значение столбцаamount
таблицыsupply
), но и пересчитать их цену (для каждой книги найти сумму цен из таблицbook
иsupply
и разделить на 2).
UPDATE book, supply
SET book.amount = book.amount + supply.amount,
book.price = (book.price + supply.price)/2
WHERE book.title = supply.title AND book.author = supply.author;
SELECT * FROM book;
Удалить из таблицы
supply
книги тех авторов, общее количество экземпляров книг которых в таблицеbook
превышает 10.
DELETE FROM supply
WHERE author IN(
SELECT author
FROM book
GROUP BY author
HAVING SUM(amount) > 10
);
SELECT * FROM supply;
Создать таблицу заказ (
ordering
), куда включить авторов и названия тех книг, количество экземпляров которых в таблицеbook
меньше среднего количества экземпляров книг в таблицеbook
. В таблицу включить столбецamount,
в котором для всех книг указать одинаковое значение — среднее количество экземпляров книг в таблицеbook
.
CREATE TABLE ordering AS
SELECT author, title,
(SELECT ROUND(AVG(amount))
FROM book) AS amount
FROM book
WHERE amount < (SELECT AVG(amount)
FROM book);
SELECT * FROM ordering;
Придумайте один или несколько запросов корректировки данных к таблицам
book
иsupply
. Проверьте, правильно ли они работают.
DELETE FROM supply
WHERE title = 'Идиот';
SELECT * FROM supply