Разобрали ответы и решения урока 2.5(База данных «Интернет-магазин книг», запросы корректировки) под курс «Интерактивный тренажер по SQL».
Предыдущий разбор модуля 2.4 следующий разбор модуль 3.1
Включить нового человека в таблицу с клиентами. Его имя Попов Илья, его email popov@test, проживает он в Москве.
INSERT INTO client(name_client,city_id,email)
SELECT 'Попов Илья', city_id, 'popov@test'
FROM city
WHERE name_city = 'Москва';
SELECT * FROM client
Создать новый заказ для Попова Ильи. Его комментарий для заказа: «Связаться со мной по вопросу доставки».
INSERT buy (buy_description, client_id)
SELECT 'Связаться со мной по вопросу доставки', client_id
FROM client
WHERE name_client IN ('Попов Илья');
В таблицу
buy_book
добавить заказ с номером 5. Этот заказ должен содержать книгу Пастернака «Лирика» в количестве двух экземпляров и книгу Булгакова «Белая гвардия» в одном экземпляре.
INSERT INTO buy_book (buy_id, book_id, amount)
VALUES
(5, (SELECT book_id FROM
book JOIN author USING(author_id)
WHERE title='Лирика' AND name_author LIKE 'Пастернак%'), 2),
(5, (SELECT book_id
FROM book JOIN author USING(author_id)
WHERE title='Белая Гвардия' AND name_author LIKE 'Булгаков%'), 1);
SELECT * FROM buy_book;
Количество тех книг на складе, которые были включены в заказ с номером 5, уменьшить на то количество, которое в заказе с номером 5 указано.
UPDATE book, buy_book
SET book.amount = book.amount - buy_book.amount
WHERE buy_book.buy_id = 5 AND book.book_id = buy_book.book_id;
SELECT * FROM book
Создать счет (таблицу
buy_pay
) на оплату заказа с номером 5, в который включить название книг, их автора, цену, количество заказанных книг и стоимость. Последний столбец назватьСтоимость
. Информацию в таблицу занести в отсортированном по названиям книг виде.
CREATE TABLE buy_pay AS
SELECT
title,
name_author,
book.price,
buy_book.amount,
book.price * buy_book.amount AS 'Стоимость'
FROM
buy_book
INNER JOIN book USING (book_id)
INNER JOIN author USING (author_id)
WHERE
buy_id = 5
ORDER BY title
Создать общий счет (таблицу
buy_pay
) на оплату заказа с номером 5. Куда включить номер заказа, количество книг в заказе (название столбцаКоличество
) и его общую стоимость (название столбцаИтого
). Для решения используйте ОДИН запрос.
CREATE TABLE buy_pay
SELECT buy_id, sum(buy_book.amount) as Количество, sum(book.price*buy_book.amount) as Итого
FROM buy_book
JOIN book USING(book_id)
JOIN author USING(author_id)
WHERE buy_id=5
GROUP BY 1;
SELECT * FROM buy_pay;
В таблицу
buy_step
для заказа с номером 5 включить все этапы из таблицыstep
, которые должен пройти этот заказ. В столбцыdate_step_beg
иdate_step_end
всех записей занестиNull
.
INSERT INTO buy_step (buy_id, step_id, date_step_beg, date_step_end)
SELECT buy_id, step_id, Null, Null
FROM buy
CROSS JOIN step
WHERE buy_id = 5;
В таблицу buy_step занести дату 12.04.2020 выставления счета на оплату заказа с номером 5.
Правильнее было бы занести не конкретную, а текущую дату. Это можно сделать с помощью функции Now(). Но при этом в разные дни будут вставляться разная дата, и задание нельзя будет проверить, поэтому вставим дату 12.04.2020.
UPDATE buy_step SET date_step_beg = '2020-04-12'
WHERE buy_id = 5 AND step_id = 1;
Завершить этап «Оплата» для заказа с номером 5, вставив в столбец
date_step_end
дату 13.04.2020, и начать следующий этап («Упаковка»), задав в столбцеdate_step_beg
для этого этапа ту же дату.Реализовать два запроса для завершения этапа и начала следующего. Они должны быть записаны в общем виде, чтобы его можно было применять для любых этапов, изменив только текущий этап. Для примера пусть это будет этап «Оплата».
UPDATE buy_step
SET date_step_end = IF(buy_id = 5 AND step_id = 1, '2020-04-13', date_step_end),
date_step_beg = IF(buy_id = 5 AND step_id = 2, '2020-04-13', date_step_beg);
Придумайте один или несколько запросов корректировки данных для предметной области «Интернет-магазин книг» (в таблицы занесены данные, как на этом шаге). Проверьте, правильно ли они работают.
UPDATE buy_step, step
SET buy_step.date_step_end = '2020-04-13'
WHERE buy_step.step_id = (SELECT step_id FROM step WHERE step.name_step = 'Оплата') AND buy_id = 5
;
UPDATE buy_step, step
SET buy_step.date_step_beg = '2020-04-13'
WHERE buy_step.step_id = (SELECT step_id +1 FROM step WHERE step.name_step = 'Оплата') AND buy_id = 5
;
SELECT * FROM buy_step
WHERE buy_id = 5