-- Лабораторная работа №4, дисциплина "Базы данных", СПбГУАП, весенний семестр 2019

-- Удаление представлений
-- DROP VIEW IF EXISTS View1 ...
-- ваш код здесь!
drop view if exists VIEW1;
drop view if exists VIEW2;
drop view if exists VIEW3;

-- Создание представлений
-- 1) (Добавить в таблицу "Книга" колонку "Цена") Пользователи, у которых в данный момент в корзине лежит книг на сумму, большую, чем какого-либо из совершенных ими заказов
-- CREATE VIEW View1 AS
-- SELECT ...
-- ваш код здесь!
CREATE VIEW View1 AS
SELECT ФИО
FROM Заказ natural join Пользователь natural join Пункт_корзина natural join Книга
GROUP BY Логин
having max(Общая_стоимость) < Количество_в_корзине * Цена
-- 2) Найти все книги категории "Роман", которые когда-либо заказывал пользователь Голиков Александр Сергеевич, и которые с тех пор стали дороже
-- CREATE VIEW View2 AS
-- SELECT ...
-- ваш код здесь!
CREATE VIEW View2 AS
SELECT Название
from Заказ cross join Книга_в_заказе on Заказ.Номер_заказа = Книга_в_заказе.Заказ natural join Категория_у_книг natural join Пользователь join Книга on Книга_в_заказе.ISBN = Книга.ISBN
where Название_категории = 'Роман' and ФИО = 'Голиков Александр Сергеевич'
group by ФИО
having Книга_в_заказе.Цена < Книга.Цена
-- 3) Для каждой категории вычислить количество авторов, чьи книги не переиздавались последние 10 лет
-- CREATE VIEW View3 AS
-- SELECT ...
-- ваш код здесь!
CREATE VIEW View3 AS
SELECT Название_категории, count(*)
FROM Книга natural join Категория_у_книг natural join Автор_у_книг
WHERE ФИО not in (
                  SELECT ФИО
                  FROM Книга natural join Автор_у_книг
                  WHERE Год_издания>=date('now')-10)
GROUP BY Название_категории
-- Оператор UPDATE
-- Если количество экземпляров книг в корзине у пользователя меньше, чем количество книг на складе, уменьшить его до этого количества
-- UPDATE Table1 ...
-- ваш код здесь!
UPDATE Пункт_корзина
SET Количество_в_корзине = (SELECT Кол_во_на_складе
                            FROM Книга
                            WHERE Пункт_корзина.ISBN=Книга.ISBN and Кол_во_на_складе < (
                                                                                        SELECT Количество_в_корзине
                                                                                        FROM Пункт_корзина));
-- Оператор DELETE
-- Удалить пользователей, которые делали только 1 заказ, и при этом более года назад.
-- DELETE FROM Table1 ...
-- ваш код здесь!
DELETE
FROM Пользователь
WHERE Логин in (SELECT Логин
                FROM Заказ
                WHERE Дата_оформления < date('now', '-1 year')
                GROUP BY Логин
                having count(*)=1)