Pastein: -- ===================================================== -- БАЗА ДАННЫХ: bookstore_lab -- ПОЛНЫЙ СКРИПТ: создание + данные + все запросы -- ===========

Загрузка данных
-- =====================================================
-- БАЗА ДАННЫХ: bookstore_lab
-- ПОЛНЫЙ СКРИПТ: создание + данные + все запросы
-- =====================================================

-- =====================================================
-- ЧАСТЬ 1. СОЗДАНИЕ ТАБЛИЦ
-- =====================================================

-- Таблица авторов
CREATE TABLE authors(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

-- Таблица жанров
CREATE TABLE genres(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Таблица книг
CREATE TABLE books(
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    price NUMERIC(10,2) CHECK (price > 0),
    author_id INT NOT NULL,
    genre_id INT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
);

-- Таблица клиентов
CREATE TABLE customers(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Таблица заказов
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    customer_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT CHECK (quantity > 0),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
);

-- =====================================================
-- ЧАСТЬ 2. ЗАПОЛНЕНИЕ ТЕСТОВЫМИ ДАННЫМИ
-- =====================================================

-- Авторы
INSERT INTO authors (first_name, last_name) VALUES
('Джордж', 'Оруэлл'),
('Джоан', 'Роулинг'),
('Лев', 'Толстой'),
('Фёдор', 'Достоевский');

-- Жанры
INSERT INTO genres (name) VALUES
('Роман'),
('Фэнтези'),
('Антиутопия'),
('Детектив');

-- Книги
INSERT INTO books (title, price, author_id, genre_id) VALUES
('1984', 350.00, 1, 3),
('Скотный двор', 250.00, 1, 3),
('Гарри Поттер и философский камень', 450.00, 2, 2),
('Война и мир', 900.00, 3, 1),
('Преступление и наказание', 550.00, 4, 1);

-- Клиенты
INSERT INTO customers (first_name, last_name, email) VALUES
('Иван', 'Петров', 'ivan@example.com'),
('Мария', 'Сидорова', 'maria@example.com'),
('Алексей', 'Смирнов', 'alex@example.com'),
('Ольга', 'Кузнецова', 'olga@example.com');

-- Заказы
INSERT INTO orders (order_date, customer_id, book_id, quantity) VALUES
('2024-01-15', 1, 1, 1),
('2024-01-20', 2, 3, 2),
('2024-02-10', 1, 4, 1),
('2024-02-15', 3, 5, 1),
('2024-03-01', 4, 2, 3);

-- Добавляем клиента без заказов (для демонстрации LEFT JOIN)
INSERT INTO customers (first_name, last_name, email) 
VALUES ('Дмитрий', 'Беззаказный', 'norders@example.com');

-- =====================================================
-- ЧАСТЬ 3. ПРОВЕРКА ДАННЫХ
-- =====================================================

SELECT 'authors' as table_name, COUNT(*) FROM authors
UNION ALL SELECT 'genres', COUNT(*) FROM genres
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'customers', COUNT(*) FROM customers
UNION ALL SELECT 'orders', COUNT(*) FROM orders;

-- =====================================================
-- ЧАСТЬ 4. АГРЕГАТНЫЕ ФУНКЦИИ
-- =====================================================

-- 1. Общее количество проданных книг
SELECT SUM(quantity) AS total_sold FROM orders;

-- 2. Средняя цена книг
SELECT ROUND(AVG(price), 2) AS avg_price FROM books;

-- 3. Самый дорогой заказ
SELECT 
    o.id,
    b.title,
    b.price * o.quantity AS total_cost
FROM orders o
JOIN books b ON o.book_id = b.id
ORDER BY total_cost DESC
LIMIT 1;

-- 4. Самый дешёвый заказ
SELECT 
    o.id,
    b.title,
    b.price * o.quantity AS total_cost
FROM orders o
JOIN books b ON o.book_id = b.id
ORDER BY total_cost ASC
LIMIT 1;

-- 5. Сумма покупок по каждому клиенту
SELECT
    c.first_name,
    c.last_name,
    SUM(b.price * o.quantity) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id

JOIN books b ON o.book_id = b.id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;

-- =====================================================
-- ЧАСТЬ 5. ОПЕРАТОР LIKE / NOT LIKE
-- =====================================================

-- 1. Email, заканчивающиеся на example.com
SELECT * FROM customers WHERE email LIKE '%example.com';

-- 2. Книги с буквой «и» в названии
SELECT title FROM books WHERE title LIKE '%и%';

-- 3. Авторы с фамилией на «Р» и с буквой «г»
SELECT * FROM authors WHERE last_name LIKE 'Р%г%';

-- 4. Книги без буквы «о»
SELECT title FROM books WHERE title NOT LIKE '%о%';

-- =====================================================
-- ЧАСТЬ 6. JOIN И LEFT JOIN
-- =====================================================

-- 1. INNER JOIN (только клиенты с заказами)
SELECT c.first_name, o.id, o.order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- 2. LEFT JOIN (все клиенты, включая без заказов)
SELECT c.first_name, c.last_name, o.id AS order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
ORDER BY c.last_name;

-- 3. Комбинированный запрос (агрегация + LEFT JOIN + LIKE)
SELECT
    c.email,
    COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.first_name LIKE '%a%'
GROUP BY c.id, c.email
ORDER BY order_count DESC;

-- =====================================================
-- ЧАСТЬ 7. UPDATE (ОБНОВЛЕНИЕ ДАННЫХ)
-- =====================================================

-- 7.1. Повысить цену книг жанра «Фэнтези» на 15%
-- Проверка ДО
SELECT b.title, b.price, g.name
FROM books b
JOIN genres g ON b.genre_id = g.id
WHERE g.name = 'Фэнтези';

-- Выполнение UPDATE
UPDATE books
SET price = price * 1.15
WHERE genre_id = (SELECT id FROM genres WHERE name = 'Фэнтези');

-- Проверка ПОСЛЕ
SELECT b.title, b.price, g.name
FROM books b
JOIN genres g ON b.genre_id = g.id
WHERE g.name = 'Фэнтези';

-- 7.2. Исправить фамилию клиента
-- Проверка
SELECT * FROM customers WHERE last_name = 'Смирнов';
-- Обновление
UPDATE customers
SET last_name = 'Смирнова'
WHERE last_name = 'Смирнов' AND first_name = 'Алексей';
-- Проверка
SELECT * FROM customers WHERE first_name = 'Алексей';

-- 7.3. Массовое обновление с CASE
-- Проверка ДО
SELECT title, price FROM books ORDER BY price;

-- Выполнение UPDATE
UPDATE books
SET price = CASE
    WHEN price < 300 THEN price * 1.10
    WHEN price BETWEEN 300 AND 600 THEN price * 1.05
    WHEN price > 600 THEN price * 0.97
    ELSE price
END;

-- Проверка ПОСЛЕ
SELECT title, price FROM books ORDER BY price;

-- =====================================================
-- ЧАСТЬ 8. DELETE (УДАЛЕНИЕ ДАННЫХ)
-- =====================================================

-- 8.1. Удалить заказы старше определённой даты
-- Проверка что будет удалено
SELECT o.id, o.order_date, c.first_name, c.last_name, b.title
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN books b ON o.book_id = b.id
WHERE o.order_date < '2024-02-01';

-- Выполнение удаления
DELETE FROM orders WHERE order_date < '2024-02-01';

-- Проверка
SELECT * FROM orders ORDER BY order_date;

-- 8.2. Удалить клиента без заказов
-- Проверка: у кого нет заказов
SELECT c.id, c.first_name, c.last_name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
HAVING COUNT(o.id) = 0;

-- Удаление
DELETE FROM customers
WHERE first_name = 'Дмитрий' AND last_name = 'Беззаказный';

-- 8.3. Удалить книги автора (каскадное удаление заказов)
-- Проверка книг Оруэлла
SELECT * FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Оруэлл');

-- Проверка заказов на эти книги
SELECT o.id, b.title FROM orders o 
JOIN books b ON o.book_id = b.id 
WHERE b.author_id = (SELECT id FROM authors WHERE last_name = 'Оруэлл');

-- Удаление (из-за ON DELETE CASCADE заказы удалятся автоматически)
DELETE FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Оруэлл');

-- Проверка
SELECT * FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Оруэлл'));
SELECT * FROM orders;

-- =====================================================
-- ЧАСТЬ 9. АРХИВАЦИЯ ДАННЫХ
-- =====================================================

-- 1. Создаём архивную таблицу
CREATE TABLE orders_archive AS
SELECT * FROM orders WHERE 1=0;

-- 2. Копируем старые заказы (до 2024-02-01) в архив
INSERT INTO orders_archive
SELECT * FROM orders WHERE order_date < '2024-02-01';

-- 3. Проверяем архив
SELECT * FROM orders_archive;

-- 4. Удаляем скопированные заказы из основной таблицы
DELETE FROM orders WHERE order_date < '2024-02-01';

-- 5. Проверяем, что удалились только старые
SELECT MIN(order_date), MAX(order_date) FROM orders;

-- =====================================================
-- ЧАСТЬ 10. ДОПОЛНИТЕЛЬНЫЕ ЗАПРОСЫ ДЛЯ АНАЛИЗА
-- =====================================================

-- 1. Самые популярные книги (по количеству продаж)
SELECT b.title, SUM(o.quantity) as total_sold
FROM books b
JOIN orders o ON b.id = o.book_id
GROUP BY b.id, b.title
ORDER BY total_sold DESC;

-- 2. Клиенты, которые потратили больше всего
SELECT c.first_name, c.last_name, SUM(b.price * o.quantity) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN books b ON o.book_id = b.id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;

-- 3. Книги, которые никогда не заказывали
SELECT b.title
FROM books b
LEFT JOIN orders o ON b.id = o.book_id
WHERE o.id IS NULL;

-- 4. Ежемесячная выручка
SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(b.price * o.quantity) as revenue
FROM orders o
JOIN books b ON o.book_id = b.id
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
Больше возможностей при регистрации:
