---

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

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

-- Создание таблицы выдач
CREATE TABLE Loans (
    loan_id SERIAL PRIMARY KEY,
    book_id INT NOT NULL,
    reader_id INT NOT NULL,
    loan_date DATE NOT NULL,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id) ON DELETE CASCADE,
    FOREIGN KEY (reader_id) REFERENCES Readers(reader_id) ON DELETE CASCADE,
    CHECK (return_date IS NULL OR return_date >= loan_date)
);
-- Заполнение таблицы Books (10 строк)
INSERT INTO Books (title, author, publication_year, copies_count) VALUES
('Война и мир', 'Лев Толстой', 1950, 5),
('Преступление и наказание', 'Фёдор Достоевский', 1965, 3),
('Мастер и Маргарита', 'Михаил Булгаков', 1973, 4),
('Евгений Онегин', 'Александр Пушкин', 1948, 6),
('Мертвые души', 'Николай Гоголь', 1955, 2),
('Отцы и дети', 'Иван Тургенев', 1960, 4),
('Герой нашего времени', 'Михаил Лермонтов', 1952, 3),
('Вишневый сад', 'Антон Чехов', 1970, 5),
('Капитанская дочка', 'Александр Пушкин', 1968, 4),
('Собачье сердце', 'Михаил Булгаков', 1989, 7);

-- Заполнение таблицы Readers (10 строк)
INSERT INTO Readers (full_name, birth_date, address, phone) VALUES
('Иванов Иван Иванович', '1995-04-12', 'г. Москва, ул. Ленина, д. 10', '+79161234567'),
('Петрова Анна Сергеевна', '1998-09-23', 'г. Москва, ул. Тверская, д. 5', '+79269876543'),
('Смирнов Алексей Павлович', '1985-02-15', 'г. Санкт-Петербург, Невский пр., д. 20', '+79112223344'),
('Кузнецова Елена Дмитриевна', '2000-11-05', 'г. Казань, ул. Баумана, д. 15', '+79374445566'),
('Васильев Дмитрий Олегович', '1992-07-30', 'г. Новосибирск, Красный пр., д. 8', '+79135556677'),
('Морозова Ольга Николаевна', '1988-12-01', 'г. Екатеринбург, ул. Ленина, д. 25', '+79227778899'),
('Соколова Мария Андреевна', '1996-03-19', 'г. Нижний Новгород, ул. Большая Покровская, д. 12', '+79031112233'),
('Попов Сергей Викторович', '1990-06-25', 'г. Самара, ул. Куйбышева, д. 30', '+79273334455'),
('Лебедева Екатерина Павловна', '2002-01-14', 'г. Омск, ул. Ленина, д. 4', '+79505556677'),
('Ковалев Андрей Михайлович', '1983-08-09', 'г. Ростов-на-Дону, Большая Садовая ул., д. 50', '+79888889900');

-- Заполнение таблицы Loans (10 строк)
INSERT INTO Loans (book_id, reader_id, loan_date, return_date) VALUES
(1, 1, '2026-01-10', '2026-01-25'),
(2, 2, '2026-02-01', NULL),
(3, 3, '2026-02-05', '2026-02-20'),
(4, 4, '2026-02-10', NULL),
(5, 5, '2026-02-12', '2026-02-28'),
(6, 6, '2026-02-15', NULL),
(7, 7, '2026-03-01', '2026-03-15'),
(8, 8, '2026-03-05', NULL),
(9, 9, '2026-03-10', '2026-03-22'),
(10, 10, '2026-03-12', NULL);
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;