SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(255), -- имя клиента birth_date DATE, -- дата рождения age INT -- возраст (можно вычислять на основе даты рождения) ); CREATE TABLE books ( book_id SERIAL PRIMARY KEY, -- уникальный идентификатор книги title VARCHAR(255) , -- название книги author VARCHAR(255) , -- автор genre VARCHAR(100), -- жанр price int, -- цена target_group VARCHAR(50) -- целевая аудитория (например, 'Дети', 'Подростки', 'Студенты', 'Пенсионеры') ); CREATE TABLE stores ( store_id SERIAL PRIMARY KEY, -- уникальный идентификатор магазина name VARCHAR(255) -- название магазина ); CREATE TABLE inventory ( inventory_id SERIAL PRIMARY KEY, store_id INT REFERENCES stores(store_id), -- ссылка на магазин book_id INT REFERENCES books(book_id), -- ссылка на книгу stock_quantity INT , -- количество на складе restock_date DATE -- дата последнего пополнения ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, store_id INT REFERENCES stores(store_id), -- ссылка на магазин order_date DATE NOT NULL, -- дата заказа customer_id INT REFERENCES customers(customer_id), -- ссылка на клиента total_amount int -- общая сумма заказа ); CREATE TABLE order_items ( order_item_id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(order_id), -- ссылка на заказ book_id INT REFERENCES books(book_id), -- ссылка на книгу quantity INT , -- количество книг price int -- цена за единицу книги на момент заказа ); CREATE TABLE sales ( sale_id SERIAL PRIMARY KEY, store_id INT REFERENCES stores(store_id), -- ссылка на магазин sale_date DATE, -- дата продажи total_amount int -- общая сумма продажи ); CREATE TABLE sale_items ( sale_item_id SERIAL PRIMARY KEY, sale_id INT REFERENCES sales(sale_id) , -- ссылка на продажу book_id INT REFERENCES books(book_id) , -- ссылка на книгу quantity INT, -- количество проданных книг price int -- цена за единицу книги на момент продажи ); INSERT INTO customers (name, birth_date, age) VALUES ('Иван Иванов', '2003-05-14', 69), ('Мария Петрова', '2001-03-25', 83), ('Елена Кузнецова', '2018-11-30', 11), ('Сергей Смирнов', '2000-07-18', 24), ('Анна Соколова', '2012-09-07', 16), ('Дмитрий Павлов', '2009-01-12', 2), ('Ольга Попова', '1985-04-23', 18), ('Павел Орлов', '1978-02-17', 20), ('Ирина Белова', '2019-06-09', 20), ('Алексей Морозов', '2002-08-01', 22); INSERT INTO books (title, author, genre, price, target_group) VALUES ('Гарри Поттер и философский камень', 'Дж. К. Роулинг', 'Фэнтези', 500, 'Подростки'), ('Над пропастью во ржи', 'Дж. Д. Сэлинджер', 'Художественная литература', 350, 'Студенты'), ('Великий Гэтсби', 'Ф. Скотт Фицджеральд', 'Классика', 400, 'Студенты'), ('Кот в шляпе', 'Доктор Сьюз', 'Детская литература', 150, 'Дети'), ('Убить пересмешника', 'Харпер Ли', 'Художественная литература', 450, 'Студенты'), ('1984', 'Джордж Оруэлл', 'Дистопия', 600, 'Студенты'), ('Краткая история времени', 'Стивен Хокинг', 'Научная литература', 700, 'Студенты'), ('Старик и море', 'Эрнест Хемингуэй', 'Художественная литература', 400, 'Пенсионеры'), ('Гордость и предубеждение', 'Джейн Остин', 'Классика', 350, 'Студенты'), ('Война и мир', 'Лев Толстой', 'Исторический роман', 800, 'Пенсионеры'); INSERT INTO stores (name) VALUES ('Книжный мир'), ('Лабиринт знаний'), ('Книга на полке'), ('Чтение для всех'), ('Магия книг'), ('Книгоцентр'), ('Точка на карте'), ('Вдохновение'), ('Светлый путь'), ('Мудрость в книгах'); INSERT INTO inventory (store_id, book_id, stock_quantity, restock_date) VALUES (1, 1, 50, '2024-09-01'), (1, 2, 30, '2024-08-20'), (1, 3, 40, '2024-09-10'), (1, 4, 60, '2024-09-15'), (1, 5, 25, '2024-09-05'), (2, 6, 15, '2024-09-03'), (2, 7, 20, '2024-09-12'), (2, 8, 35, '2024-09-14'), (3, 9, 55, '2024-09-06'), (3, 10, 45, '2024-09-08'); INSERT INTO orders (store_id, order_date, customer_id, total_amount) VALUES (1, '2024-10-01', 1, 1200), (1, '2024-10-02', 2, 950), (1, '2024-10-03', 3, 1250), (1, '2024-10-04', 4, 500), (2, '2024-10-05', 5, 1100), (2, '2024-10-06', 6, 800), (2, '2024-10-07', 7, 1350), (3, '2024-10-08', 8, 600), (3, '2024-10-09', 9, 1150), (3, '2024-10-10', 10, 950); INSERT INTO order_items (order_id, book_id, quantity, price) VALUES (1, 1, 2, 500), (1, 5, 1, 450), (2, 2, 3, 350), (2, 4, 1, 150), (3, 6, 1, 600), (3, 3, 2, 400), (4, 9, 1, 400), (5, 7, 1, 700), (5, 8, 1, 400), (6, 10, 2, 350); INSERT INTO sales (store_id, sale_date, total_amount) VALUES (1, '2024-10-05', 1500), (1, '2024-10-06', 2000), (2, '2024-10-07', 1200), (2, '2024-10-08', 1300), (3, '2024-10-09', 1600), (3, '2024-10-10', 1800), (4, '2024-10-11', 1400), (4, '2024-10-12', 1100), (5, '2024-10-13', 2200), (5, '2024-10-14', 2500); INSERT INTO sale_items (sale_id, book_id, quantity, price) VALUES (1, 1, 2, 500), (1, 2, 1, 350), (2, 3, 1, 400), (2, 4, 2, 150), (3, 5, 1, 450), (3, 6, 2, 600), (4, 7, 1, 700), (4, 8, 1, 400), (5, 9, 1, 400), (5, 10, 2, 350); CREATE OR REPLACE FUNCTION delete_related_items_on_author_removal() RETURNS TRIGGER AS $$ BEGIN -- Удаляем все записи из таблицы order_items, которые ссылаются на удаляемую книгу DELETE FROM order_items WHERE book_id = OLD.book_id; -- Удаляем все записи из таблицы sale_items, которые ссылаются на удаляемую книгу DELETE FROM sale_items WHERE book_id = OLD.book_id; -- Удаляем все записи из таблицы inventory, которые ссылаются на удаляемую книгу DELETE FROM inventory WHERE book_id = OLD.book_id; -- Возвращаем OLD (удаляем запись из таблицы books) RETURN OLD; END; $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS trigger_delete_related_items ON books; CREATE TRIGGER trigger_delete_related_items BEFORE DELETE ON books FOR EACH ROW EXECUTE FUNCTION delete_related_items_on_author_removal(); DELETE FROM books WHERE book_id = 1;