---

-- Создание таблицы книг
CREATE TABLE Books (
    book_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    year_published INT CHECK (year_published > 1900),
    copies_count INT CHECK (copies_count >= 0) NOT NULL
);

-- Создание таблицы читателей
CREATE TABLE Readers (
    reader_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    birth_date DATE NOT NULL,
    address TEXT NOT NULL,
    phone VARCHAR(30) NOT NULL
);

-- Создание таблицы выдач
CREATE TABLE Loans (
    loan_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    book_id INT NOT NULL REFERENCES Books(book_id) ON DELETE RESTRICT,
    reader_id INT NOT NULL REFERENCES Readers(reader_id) ON DELETE RESTRICT,
    loan_date DATE NOT NULL,
    return_date DATE,
    CHECK (return_date IS NULL OR return_date >= loan_date)
);
-- Вставка данных в таблицу Books (12 строк)
INSERT INTO Books (title, author, year_published, copies_count) VALUES
('1984', 'Джордж Оруэлл', 1949, 4),
('Три товарища', 'Эрих Мария Ремарк', 1936, 5),
('Мастер и Маргарита', 'Михаил Булгаков', 1967, 3),
('Властелин колец', 'Джон Р. Р. Толкин', 1954, 6),
('Сто лет одиночества', 'Габриэль Гарсия Маркес', 1967, 2),
('Над пропастью во ржи', 'Джером Сэлинджер', 1951, 3),
('Великий Гэтсби', 'Фрэнсис Скотт Фицджеральд', 1925, 4),
('Цветы для Элджернона', 'Дэниел Киз', 1966, 5),
('Вино из одуванчиков', 'Рэй Брэдбери', 1957, 4),
('Алхимик', 'Пауло Коэльо', 1988, 3),
('Гарри Поттер и философский камень', 'Джоан Роулинг', 1997, 7),
('Метро 2033', 'Дмитрий Глуховский', 2005, 5);

-- Вставка данных в таблицу Readers (10 строк)
INSERT INTO Readers (full_name, birth_date, address, phone) VALUES
('Иванов Иван Иванович', '1995-05-12', 'г. Москва, ул. Ленина, д. 5', '+7-999-111-22-33'),
('Петрова Анна Сергеевна', '1998-09-23', 'г. Москва, ул. Мира, д. 10', '+7-999-222-33-44'),
('Смирнов Алексей Петрович', '1985-02-14', 'г. Санкт-Петербург, Невский пр., д. 20', '+7-911-333-44-55'),
('Кузнецова Елена Дмитриевна', '2000-11-05', 'г. Казань, ул. Баумана, д. 15', '+7-987-444-55-66'),
('Васильев Дмитрий Олегович', '1992-07-30', 'г. Екатеринбург, ул. Вайнера, д. 8', '+7-912-555-66-77'),
('Морозова Ольга Николаевна', '1988-12-19', 'г. Новосибирск, Красный пр., д. 25', '+7-913-666-77-88'),
('Соколова Екатерина Павловна', '1999-04-03', 'г. Нижний Новгород, ул. Большая Покровская, д. 12', '+7-910-777-88-99'),
('Попов Сергей Александрович', '1991-08-16', 'г. Самара, ул. Ленинградская, д. 18', '+7-927-888-99-00'),
('Лебедева Мария Андреевна', '2001-01-27', 'г. Казань, ул. Чистопольская, д. 30', '+7-987-999-00-11'),
('Козлов Артем Витальевич', '1987-06-22', 'г. Москва, ул. Тверская, д. 4', '+7-999-000-11-22');

-- Вставка данных в таблицу Loans (12 строк, включая активные выдачи с return_date IS NULL)
INSERT INTO Loans (book_id, reader_id, loan_date, return_date) VALUES
(1, 1, '2026-10-01', '2026-10-15'),
(2, 2, '2026-10-05', NULL), -- активная выдача
(3, 3, '2026-10-10', '2026-10-20'),
(4, 4, '2026-10-12', NULL), -- активная выдача
(1, 5, '2026-10-15', NULL), -- активная выдача
(5, 6, '2026-09-01', '2026-09-15'),
(6, 7, '2026-10-18', NULL), -- активная выдача
(2, 8, '2026-09-20', '2026-10-02'),
(7, 9, '2026-10-22', NULL), -- активная выдача
(8, 10, '2026-09-10', '2026-09-25'),
(4, 1, '2026-10-23', NULL), -- активная выдача
(9, 3, '2026-10-01', '2026-10-10');
SELECT title, author, copies_count 
FROM Books;
SELECT 
    l.loan_id, 
    b.title AS book_title, 
    r.full_name AS reader_name, 
    l.loan_date 
FROM Loans l
JOIN Books b ON l.book_id = b.book_id
JOIN Readers r ON l.reader_id = r.reader_id
WHERE l.return_date IS NULL;
SELECT 
    b.author, 
    COUNT(l.loan_id) AS total_loans
FROM Loans l
JOIN Books b ON l.book_id = b.book_id
GROUP BY b.author
ORDER BY total_loans DESC
LIMIT 5;