Pastein: SET GLOBAL log_bin_trust_function_creators = 1; DROP DATABASE IF EXISTS library; CREATE DATABASE library CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode

Загрузка данных
SET GLOBAL log_bin_trust_function_creators = 1;

DROP DATABASE IF EXISTS library;
CREATE DATABASE library CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE library;


CREATE TABLE authors (
    author_id  INT PRIMARY KEY AUTO_INCREMENT,
    full_name  VARCHAR(150) NOT NULL,
    country    VARCHAR(100)
);

CREATE TABLE books (
    book_id          INT PRIMARY KEY AUTO_INCREMENT,
    title            VARCHAR(200) NOT NULL,
    genre            VARCHAR(50),
    publication_year INT,
    price            DECIMAL(10,2),
    rating           DECIMAL(3,2)
);

CREATE TABLE book_authors (
    book_id   INT,
    author_id INT,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id)   REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE readers (
    reader_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    age       INT
);

CREATE TABLE loans (
    loan_id     INT PRIMARY KEY AUTO_INCREMENT,
    book_id     INT,
    reader_id   INT,
    issue_date  DATE NOT NULL,
    return_date DATE,
    fine        DECIMAL(10,2) DEFAULT 0,
    FOREIGN KEY (book_id)   REFERENCES books(book_id),
    FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
);

-- Заполнение таблиц данными


INSERT INTO authors (full_name, country) VALUES
('Лев Толстой',        'Россия'),
('Фёдор Достоевский',  'Россия'),
('Джордж Оруэлл',      'Великобритания'),
('Айзек Азимов',       'США'),
('Артур Конан Дойл',   'Великобритания');

INSERT INTO books (title, genre, publication_year, price, rating) VALUES
('Война и мир',             'Роман',      1869, 950.00, 4.8),
('Анна Каренина',           'Роман',      1877, 780.00, 4.6),
('Преступление и наказание','Роман',      1866, 690.00, 4.7),
('1984',                    'Антиутопия', 1949, 540.00, 4.9),
('Скотный двор',            'Антиутопия', 1945, 410.00, NULL),
('Я, робот',                'Фантастика', 1950, 620.00, 4.4),
('Основание',               'Фантастика', 1951, 700.00, 4.5),
('Записки о Шерлоке Холмсе','Детектив',   1892, 480.00, 4.3);

INSERT INTO book_authors (book_id, author_id) VALUES
(1,1),(2,1),(3,2),(4,3),(5,3),(6,4),(7,4),(8,5);

INSERT INTO readers (full_name, age) VALUES
('Иван Петров',     19),
('Мария Сидорова',  34),
('Олег Кузнецов',   67),
('Анна Смирнова',   25),
('Павел Орлов',     60);

INSERT INTO loans (book_id, reader_id, issue_date, return_date) VALUES
(1, 1, '2024-01-01', '2024-01-05'),
(1, 2, '2024-02-01', '2024-02-20'),
(1, 4, '2024-05-01', NULL),
(2, 1, '2024-03-10', '2024-03-15'),
(3, 3, '2024-04-01', '2024-04-12'),
(4, 1, '2024-06-01', NULL),
(6, 5, '2024-06-01', '2024-06-03'),
(1, 3, '2024-07-01', '2024-07-04'),
(2, 1, '2024-08-01', NULL);


-- ЗАДАНИЕ 1
-- Функция: GetBookLoanCount — принимает id книги, возвращает количество её выдач.
-- Процедура: ShowBookPopularity — проверяет существование книги, создаёт таблицу
--           temp_popularity и выводит популярность книги.

DELIMITER $$
CREATE FUNCTION GetBookLoanCount(p_book_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE v_count INT;
    SELECT COUNT(*) INTO v_count FROM loans WHERE book_id = p_book_id;
    RETURN v_count;
END$$

CREATE PROCEDURE ShowBookPopularity(IN p_book_id INT)
BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM books WHERE book_id = p_book_id;
    IF v_exists = 0 THEN
        SELECT CONCAT('Ошибка: книга с id = ', p_book_id, ' не найдена') AS message;
    ELSE
        DROP TEMPORARY TABLE IF EXISTS temp_popularity;
        CREATE TEMPORARY TABLE temp_popularity (
            book_id    INT,
            popularity INT		);
        INSERT INTO temp_popularity (book_id, popularity)
        VALUES (p_book_id, GetBookLoanCount(p_book_id));
        SELECT * FROM temp_popularity;
    END IF;
END$$
DELIMITER ;


-- ЗАДАНИЕ 2
-- Функция: GetAgeCategory — принимает возраст, возвращает категорию. Процедура: UpdateAgeCategories — добавляет колонку age_category и обновляет её.
DELIMITER $$
CREATE FUNCTION GetAgeCategory(p_age INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE v_category VARCHAR(20);
    IF p_age < 25 THEN
        SET v_category = 'Молодой';
    ELSEIF p_age <= 60 THEN
        SET v_category = 'Взрослый';
    ELSE
        SET v_category = 'Пенсионер';
    END IF;
    RETURN v_category;
END$$

CREATE PROCEDURE UpdateAgeCategories()
BEGIN
    SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name   = 'readers'
      AND column_name  = 'age_category'
	);
	IF @col_exists = 0 THEN						
		ALTER TABLE readers ADD COLUMN age_category VARCHAR(20);
	END IF;
    UPDATE readers SET age_category = GetAgeCategory(age);
    SELECT reader_id, full_name, age, age_category FROM readers;
END$$
DELIMITER ;


-- ЗАДАНИЕ 3
-- Функция: CalculateFine — вычисляет штраф по дате выдачи и возврата.
-- Процедура: RecalculateFines — пересчитывает штрафы для проблемных выдач.

DELIMITER $$
CREATE FUNCTION CalculateFine(p_issue_date DATE, p_return_date DATE)
RETURNS DECIMAL(10,2)
DETERMINISTIC

BEGIN
    DECLARE v_end  DATE;
    DECLARE v_days INT;
    IF p_return_date IS NULL THEN
        SET v_end = CURDATE();
    ELSE
        SET v_end = p_return_date;
    END IF;
    SET v_days = DATEDIFF(v_end, p_issue_date);
    IF v_days <= 7 THEN
        RETURN 0;
    END IF;
    RETURN (v_days - 7) * 1.00;
END$$

CREATE PROCEDURE RecalculateFines()
BEGIN
    UPDATE loans
    SET fine = CalculateFine(issue_date, return_date)
    WHERE return_date IS NULL
       OR DATEDIFF(return_date, issue_date) > 7;

    SELECT loan_id, book_id, reader_id, issue_date, return_date, fine
    FROM loans
    WHERE return_date IS NULL OR fine > 0
    ORDER BY fine DESC;
END$$
DELIMITER ;


-- ЗАДАНИЕ 4
-- Функция: GetAuthorAvgRating — вычисляет средний рейтинг книг автора.
-- Процедура: BuildAuthorsRating — создаёт таблицу с рейтингами авторов.


DELIMITER $$

CREATE FUNCTION GetAuthorAvgRating(p_author_id INT)
RETURNS DECIMAL(4,2)
READS SQL DATA
BEGIN
    DECLARE v_avg DECIMAL(4,2);
    SELECT AVG(b.rating) INTO v_avg
    FROM books b
    JOIN book_authors ba ON b.book_id = ba.book_id
    WHERE ba.author_id = p_author_id
      AND b.rating IS NOT NULL;
    RETURN COALESCE(v_avg, 0);
END$$

CREATE PROCEDURE BuildAuthorsRating()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS author_rating;
    CREATE TEMPORARY TABLE author_rating (
        author_id   INT,
        author_name VARCHAR(150),
        avg_rating  DECIMAL(4,2)
    );
    INSERT INTO author_rating (author_id, author_name, avg_rating)
    SELECT a.author_id, a.full_name, GetAuthorAvgRating(a.author_id)
    FROM authors a;
    SELECT * FROM author_rating ORDER BY avg_rating DESC;
END$$

DELIMITER ;

-- ЗАДАНИЕ 5
-- Функция: CountReaderGenre — считает, сколько раз читатель брал книги жанра.
-- Процедура: RecommendBooks — рекомендует топ-3 книги для читателя.


DELIMITER $$
CREATE FUNCTION CountReaderGenre(p_reader_id INT, p_genre VARCHAR(50))
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE v_count INT;
    SELECT COUNT(*) INTO v_count
    FROM loans l
    JOIN books b ON l.book_id = b.book_id
    WHERE l.reader_id = p_reader_id
      AND b.genre = p_genre;
    RETURN v_count;
END$$

CREATE PROCEDURE RecommendBooks(IN p_reader_id INT)
BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM readers WHERE reader_id = p_reader_id;
    IF v_exists = 0 THEN
        SELECT CONCAT('Ошибка: читатель с id = ', p_reader_id, ' не найден') AS message;
    ELSE
        SELECT b.book_id,
               b.title,
               b.genre,
               CountReaderGenre(p_reader_id, b.genre) AS genre_match
        FROM books b
        ORDER BY genre_match DESC, b.rating DESC
        LIMIT 3;
    END IF;
END$$
DELIMITER ;


-- ЗАДАНИЕ 6
-- Функция: SumBooksPriceAfterYear — суммирует цены книг после заданного года.
-- Процедура: SaveLibraryStats — сохраняет статистику в таблицу library_stats.

DELIMITER $$
CREATE FUNCTION SumBooksPriceAfterYear(p_year INT)
RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
    DECLARE v_sum DECIMAL(12,2);
    SELECT SUM(price) INTO v_sum
    FROM books
    WHERE publication_year > p_year;
    RETURN COALESCE(v_sum, 0);
END$$

CREATE PROCEDURE SaveLibraryStats(IN p_year INT)
BEGIN
    DECLARE v_count INT;
    DECLARE v_sum   DECIMAL(12,2);
    SELECT COUNT(*) INTO v_count FROM books WHERE publication_year > p_year;
    IF v_count = 0 THEN
        SELECT CONCAT('Нет книг, изданных после ', p_year, ' года') AS message;
    ELSE
        CREATE TABLE IF NOT EXISTS library_stats (
            id           INT PRIMARY KEY AUTO_INCREMENT,
            after_year   INT,
            books_count  INT,
            total_price  DECIMAL(12,2),
            calc_date    DATETIME DEFAULT CURRENT_TIMESTAMP
        );
        SET v_sum = SumBooksPriceAfterYear(p_year);
        INSERT INTO library_stats (after_year, books_count, total_price)
        VALUES (p_year, v_count, v_sum);
        SELECT * FROM library_stats;
    END IF;
END$$
DELIMITER ;


-- Вызов процедур для проверки


CALL ShowBookPopularity(1);
CALL ShowBookPopularity(999);

SET SQL_SAFE_UPDATES = 0;

CALL UpdateAgeCategories();
CALL UpdateAgeCategories();
CALL RecalculateFines();
CALL BuildAuthorsRating();
CALL RecommendBooks(1);
CALL SaveLibraryStats(1900);

SET SQL_SAFE_UPDATES = 1;
Больше возможностей при регистрации:
