script.sql

DROP DATABASE IF EXISTS HUSTLIBRARY ;
CREATE DATABASE HUSTLIBRARY;
USE HUSTLIBRARY;

CREATE TABLE TACGIA
(
    MATG CHAR(7) NOT NULL PRIMARY KEY,
    TENTG VARCHAR(30) NOT NULL
);

LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/tacgia.txt' 
INTO TABLE TACGIA
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(MATG, TENTG);

CREATE TABLE VITRI
(
    MAVT CHAR(9) NOT NULL PRIMARY KEY, 
    TENVT VARCHAR(30) NOT NULL
);
-- TxDyGzNw(Tầng 1 - Day 1 - Giá 2 - Ngăn 3)
LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/vitri.txt' 
INTO TABLE VITRI
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(MAVT, TENVT);

CREATE TABLE CN
(
    MACN CHAR(4) NOT NULL PRIMARY KEY,
    TENCN VARCHAR(40) NOT NULL
);

LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/chuyennganh.csv' 
INTO TABLE CN
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(MACN, TENCN);

CREATE TABLE NXB
(
    MANXB CHAR(5) NOT NULL PRIMARY KEY,
    TENNXB VARCHAR(60) NOT NULL,
    DIACHI VARCHAR(150) NOT NULL,
    WEBSITE VARCHAR(80),
    PHONE CHAR(10) NOT NULL
);

LOAD DATA LOCAL INFILE  
'/home/fuurinkazan/Documents/MySql/Project/data/NhaXuatBan.csv'
INTO TABLE NXB  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(MANXB, TENNXB, DIACHI, WEBSITE, PHONE);

CREATE TABLE BOOK
(
    BID CHAR(7) NOT NULL PRIMARY KEY,
    BNAME VARCHAR(400) NOT NULL,
    SL SMALLINT NOT NULL,
    GIA INT NOT NULL,
    NAMXB INT NOT NULL,
    LANG VARCHAR(20) NOT NULL,
    PAGE SMALLINT NOT NULL,
    MAVT CHAR(9) NOT NULL,
    MACN CHAR(4) NOT NULL,
    MANXB CHAR(5) NOT NULL,
    CONSTRAINT FK_BCN FOREIGN KEY(MACN) REFERENCES CN(MACN) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT FK_BVT FOREIGN KEY(MAVT) REFERENCES VITRI(MAVT) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT FK_BNXB FOREIGN KEY(MANXB) REFERENCES NXB(MANXB) ON UPDATE CASCADE ON DELETE CASCADE
);

LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/book.txt' 
INTO TABLE BOOK
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(MANXB,MACN,MAVT,PAGE,LANG,NAMXB,GIA,SL,BID,BNAME);

CREATE TABLE BTG
(
    BID CHAR(7) NOT NULL,
    MATG CHAR(7) NOT NULL,
    PRIMARY KEY(BID, MATG),
    CONSTRAINT FK_BTGTG FOREIGN KEY(MATG) REFERENCES TACGIA(MATG) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT FK_BTGB FOREIGN KEY(BID) REFERENCES BOOK(BID) ON UPDATE CASCADE ON DELETE CASCADE
);

LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/btg.txt' 
INTO TABLE BTG
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(BID, MATG);

CREATE TABLE NHANVIEN
(
    MANV CHAR(5) PRIMARY KEY NOT NULL,
    HOTEN VARCHAR(30) NOT NULL,
    NGAYSINH DATE NOT NULL,
    EMAIL VARCHAR(50) NOT NULL,
    PHONE CHAR(10) NOT NULL
);

LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/nhanvien.txt' 
INTO TABLE NHANVIEN
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(MANV,HOTEN,NGAYSINH,EMAIL,PHONE);

CREATE TABLE KHOA
(
    MAKHOA VARCHAR(8) NOT NULL PRIMARY KEY,
    TENKHOA VARCHAR(50) NOT NULL
);

LOAD DATA LOCAL INFILE  
'/home/fuurinkazan/Documents/MySql/Project/data/Khoa.csv'
INTO TABLE KHOA  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(MAKHOA, TENKHOA);

CREATE TABLE STUDENT
(
    MSSV INT NOT NULL PRIMARY KEY,
    HOTEN VARCHAR(30) NOT NULL,
    NGAYSINH DATE NOT NULL,
    MAKHOA VARCHAR(8) NOT NULL,
    EMAIL VARCHAR(50) NOT NULL,
    CONSTRAINT FK_SVK FOREIGN KEY(MAKHOA) REFERENCES KHOA(MAKHOA) ON UPDATE CASCADE ON DELETE CASCADE
);

LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/sinhvien.txt' 
INTO TABLE STUDENT
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(MSSV, HOTEN, NGAYSINH, MAKHOA, EMAIL);

CREATE TABLE MUONTRA
(
    MANV CHAR(5) NOT NULL,
    MSSV INT NOT NULL,
    BID CHAR(7) NOT NULL,
    NMUON DATE NOT NULL,
    NTRA DATE DEFAULT NULL,
    HANTRA DATE NOT NULL,
    PRIMARY KEY(MANV, MSSV, BID)
);

ALTER TABLE MUONTRA ADD HANTRA DATE NOT NULL;
UPDATE MUONTRA SET HANTRA = NMUON + INTERVAL 4 MONTH;

LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/muontra4.txt' 
INTO TABLE MUONTRA
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(MANV, MSSV, BID, NMUON);

-- LOAD DATA LOCAL INFILE '/home/fuurinkazan/Documents/MySql/Project/data/muontra2.txt' 
-- INTO TABLE MUONTRA
-- FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
-- (MANV, MSSV, BID, NMUON, NTRA);

    -- CONSTRAINT FK_MTNV FOREIGN KEY(MANV) REFERENCES NHANVIEN(MANV) ON UPDATE CASCADE ON DELETE CASCADE,
    -- CONSTRAINT FK_MTSV FOREIGN KEY(MSSV) REFERENCES STUDENT(MSSV) ON UPDATE CASCADE ON DELETE CASCADE,
    -- CONSTRAINT FK_MTB FOREIGN KEY(BID) REFERENCES BOOK(BID) ON UPDATE CASCADE ON DELETE CASCADE

--1 Tìm quyển sách đắt nhất của mỗi tác giả 
SELECT TENTG, IF(CHAR_LENGTH(BNAME) > 40, CONCAT(SUBSTRING(BNAME, 1, 40), '...'), BNAME) AS TITLE, GIA FROM TACGIA, BTG, BOOK, (SELECT MATG, MAX(GIA) AS MAX_GIA FROM BTG, BOOK WHERE BTG.BID = BOOK.BID GROUP BY MATG) AS TMP WHERE TACGIA.MATG = BTG.MATG AND BTG.BID = BOOK.BID AND TACGIA.MATG = TMP.MATG AND BOOK.GIA = TMP.MAX_GIA;
/*
SELECT 
    TENTG, 
    IF(CHAR_LENGTH(BNAME) > 40, CONCAT(SUBSTRING(BNAME, 1, 40), '...'), BNAME) AS TITLE, 
    GIA 
FROM 
    TACGIA, 
    BTG, 
    BOOK, 
    (SELECT MATG, MAX(GIA) AS MAX_GIA FROM BTG, BOOK WHERE BTG.BID = BOOK.BID GROUP BY MATG) AS TMP 
WHERE 
    TACGIA.MATG = BTG.MATG AND 
    BTG.BID = BOOK.BID AND 
    TACGIA.MATG = TMP.MATG AND 
    BOOK.GIA = TMP.MAX_GIA;
*/

-- 2 Thống kế % số lượng sinh viên đọc sách mỗi tháng trong 1 năm 
DELIMTER # CREATE PROCEDURE COUNT_STUDENT_PER_MONTH(y INT) BEGIN DECLARE sumStudent, nStudentMonth INT; IF(NOT EXISTS(SELECT * FROM MUONTRA WHERE YEAR(NMUON) = y)) THEN SELECT 'NAM KHONG TON TAI' AS 'NOTICE'; ELSE SET sumStudent = (SELECT COUNT(MSSV) FROM MUONTRA WHERE YEAR(NMUON) = y); SELECT MONTH(NMUON) AS MONTHS, CONCAT(ROUND(COUNT(MSSV) * 100 / sumStudent, 2), '%') AS PERCENT, REPEAT('*',(COUNT(MSSV))*100/sumStudent) AS 'PERCENT HISTOGRAM' FROM MUONTRA WHERE YEAR(NMUON) = y GROUP BY MONTHS ORDER BY MONTHS; END IF; END# DELIMTER ;
CALL COUNT_STUDENT_PER_MONTH(2020);
/*
DELIMTER # 
CREATE PROCEDURE COUNT_STUDENT_PER_MONTH(IN y INT) 
BEGIN 
    DECLARE sumStudent, nStudentMonth INT; 
    SET sumStudent = (SELECT COUNT(MSSV) FROM MUONTRA WHERE YEAR(NMUON) = y); 
    SELECT 
        MONTH(NMUON) AS MONTHS, 
        CONCAT(ROUND(COUNT(MSSV) * 100 / sumStudent, 2), '%') AS PERCENT, 
        REPEAT('*', (COUNT(MSSV)) * 100/ sumStudent) AS 'PERCENT HISTOGRAM' 
    FROM MUONTRA WHERE YEAR(NMUON) = y GROUP BY MONTHS ORDER BY MONTHS; 
END# 
DELIMITER ;
*/


-- 3 So sánh tỉ lệ trả sách đúng hạn trong 2 năm liên tiếp 
DELIMITER #
CREATE FUNCTION TL_TRA_SACH(YEAR1 INT, YEAR2 INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE MOTA VARCHAR(100);
    IF(NOT EXISTS(SELECT * FROM MUONTRA WHERE YEAR(NMUON) = YEAR1)) THEN SET MOTA = CONCAT('NAM ', YEAR1, ' KHONG TON TAI');
    ELSEIF(NOT EXISTS(SELECT * FROM MUONTRA WHERE YEAR(NMUON) = YEAR2)) THEN SET MOTA = CONCAT('NAM ', YEAR2, ' KHONG TON TAI');
    ELSE
        BEGIN
            DECLARE TL1, TL2 DECIMAL(10,2);
            SET TL1 = (SELECT DUNG_HAN.COUNT_DUNG_HAN * 100 / TONG_TRA FROM (SELECT COUNT(*) AS COUNT_DUNG_HAN FROM MUONTRA WHERE YEAR(NTRA) = YEAR1 AND NTRA <= HANTRA) DUNG_HAN, (SELECT COUNT(*) AS TONG_TRA FROM MUONTRA WHERE YEAR(NTRA) = YEAR1) TONG);

            SET TL2 = (SELECT DUNG_HAN.COUNT_DUNG_HAN * 100 / TONG_TRA FROM (SELECT COUNT(*) AS COUNT_DUNG_HAN FROM MUONTRA WHERE YEAR(NTRA) = YEAR2 AND NTRA <= HANTRA) DUNG_HAN, (SELECT COUNT(*) AS TONG_TRA FROM MUONTRA WHERE YEAR(NTRA) = YEAR2) TONG);
            
            IF TL2 > TL1 THEN 
                SET MOTA = CONCAT('NAM ', YEAR2, '(', TL2,'%)', ' TANG ', ROUND(TL2 - TL1, 2), '%', ' SO VOI NAM ', YEAR1, '(', TL1,'%)');
            ELSEIF TL2 < TL1 THEN 
                SET MOTA = CONCAT('NAM ', YEAR2, '(', TL2,'%)', ' GIAM ', ROUND(TL1 - TL2, 2), '%', ' SO VOI NAM ', YEAR1, '(', TL1,'%)');
            ELSE SET MOTA = 'TI LE KHONG DOI';
            END IF;
        END;
    END IF;
    RETURN MOTA;
END #
DELIMITER ;
RETURN (MOTA);

SELECT TL_TRA_SACH(2020, 2021) AS 'TI LE TRA SACH DUNG HAN';



-- 2 Thống kế % số lượng sinh viên đọc sách mỗi tháng trong 1 năm 
DROP PROCEDURE COUNT_STUDENT_PER_MONTH;
DELIMITER # 
CREATE PROCEDURE COUNT_STUDENT_PER_MONTH(y INT) 
BEGIN 
DECLARE sumStudent INT; 
IF(NOT EXISTS(SELECT * FROM MUONTRA WHERE YEAR(NMUON) = y)) 
THEN SELECT 'NAM KHONG TON TAI' AS 'NOTICE'; 
ELSE SET sumStudent = (SELECT COUNT(MSSV) FROM MUONTRA WHERE YEAR(NMUON) = y); 
SELECT MONTH(NMUON) AS MONTHS, CONCAT(ROUND(COUNT(MSSV) * 100 / sumStudent, 2), '%') AS PERCENT, REPEAT('*',(COUNT(MSSV))*100/sumStudent) AS 'PERCENT HISTOGRAM' 
FROM MUONTRA WHERE YEAR(NMUON) = y 
GROUP BY MONTHS ORDER BY MONTHS;
END IF; 
END# 
DELIMITER ;
CALL COUNT_STUDENT_PER_MONTH(2021);

--4 Procedure cho tìm kiếm sách theo 3 mode mặc định là liên quan nhất tiếp theo là mới nhất(NEWEST) và được mượn nhiều nhất(MOST_BORROW)
DROP PROCEDURE SEARCH_BOOK;
DELIMITER #
CREATE PROCEDURE SEARCH_BOOK(IN SEARCH_KEY VARCHAR(100),IN MODE VARCHAR(15)) BEGIN SELECT FULL.BID, FULL.BNAME AS TITLE, FULL.VT AS 'VI TRI', FULL.TENTG, SL - IFNULL(DANGMUON.SLMUON, 0) AS 'SL CON LAI', FULL.LANG, FULL.TENCN AS 'CHUYEN NGANH', FULL.NAMXB FROM (SELECT BOOK.BID AS BID, BNAME, SL, LANG, NAMXB, TENCN, GROUP_CONCAT(TENTG SEPARATOR ', ') AS TENTG, TENVT AS VT FROM BOOK, BTG, TACGIA, CN, VITRI WHERE BOOK.BID = BTG.BID AND BTG.MATG = TACGIA.MATG AND BOOK.MACN = CN.MACN AND BOOK.MAVT = VITRI.MAVT AND MATCH(BNAME) AGAINST(SEARCH_KEY) GROUP BY BTG.BID) FULL LEFT JOIN (SELECT BOOK.BID AS BOOKID, COUNT(MSSV) AS SLMUON FROM BOOK, MUONTRA WHERE BOOK.BID = MUONTRA.BID AND MATCH(BNAME) AGAINST(SEARCH_KEY) AND NTRA IS NULL  GROUP BY MUONTRA.BID) DANGMUON ON FULL.BID = DANGMUON.BOOKID ORDER BY CASE WHEN MODE = 'NEWEST' THEN FULL.NAMXB WHEN MODE = 'MOST_BORROW' THEN DANGMUON.SLMUON END DESC; END#
DELIMITER ;

CALL SEARCH_BOOK('Math', 'NEWEST')\G
CALL SEARCH_BOOK('JavaScript', '')\G


-- 5 Thống kê số lượng mượn sách mỗi ngày trong 1 tháng nhất định trong 1 năm  
DROP PROCEDURE SL_MUON_SACH_MOI_THANG;
DELIMITER #
CREATE PROCEDURE SL_MUON_SACH_MOI_THANG(INMONTH INT, INYEAR INT)
BEGIN
    DECLARE MSG VARCHAR(100);
    IF(INMONTH > 12 OR INMONTH < 1) THEN 
        SET MSG = "THANG KHONG TON TAI !!!";
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = MSG;
    ELSEIF(NOT EXISTS(SELECT * FROM MUONTRA WHERE MONTH(NMUON) = INMONTH AND YEAR(NMUON) = INYEAR)) THEN 
        SET MSG = CONCAT("SACH CHUA DUOC MUON VAO THANG ", INMONTH, " NAM ", INYEAR); 
        SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = MSG;
    ELSE 
        SELECT DAYOFMONTH(DATE_FIELD) AS 'DAY', IFNULL(TONGNMUON.SL, 0) AS 'SO LUONG' 
        FROM 
            (SELECT DATE_FIELD 
            FROM 
                (SELECT (MAKEDATE(INYEAR, 1) + INTERVAL(INMONTH - 1) MONTH + INTERVAL DAYNUM DAY) AS DATE_FIELD 
                FROM 
                    (SELECT 
                        T * 10 + U AS DAYNUM 
                        FROM 
                            (SELECT 0 AS T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A, 
                            (SELECT 0 AS U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B 
                        ORDER BY DAYNUM
                    ) TMP
                ) NGAY
            WHERE MONTH(DATE_FIELD) = INMONTH
            ) ALLDAY 
            LEFT JOIN 
            (SELECT 
                NMUON, 
                COUNT(MSSV) AS SL 
            FROM MUONTRA 
            WHERE MONTH(NMUON) = INMONTH AND YEAR(NMUON) = INYEAR 
            GROUP BY NMUON) TONGNMUON 
            ON TONGNMUON.NMUON = ALLDAY.DATE_FIELD 
        ORDER BY DATE_FIELD;
    END IF;
END #
DELIMITER ;
CALL SL_MUON_SACH_MOI_THANG(1, 2022);
CALL SL_MUON_SACH_MOI_THANG(3, 2021);


--6 Một sinh viên chỉ có thể mượn 3 quyển sách trong cùng 1 ngày.
CREATE TRIGGER CHECK_MUON_BOOK BEFORE INSERT ON MUONTRA FOR EACH ROW BEGIN DECLARE MSG VARCHAR(100); IF(EXISTS(SELECT MSSV, COUNT(MSSV) FROM MUONTRA WHERE NMUON = CURDATE() GROUP BY MSSV HAVING MSSV = NEW.MSSV AND COUNT(MSSV) >= 3)) THEN SET MSG = CONCAT(NEW.MSSV, " BORROWED 3 BOOKS TODAY. YOU CAN'T BORROW MORE BOOK !!!"); SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = MSG; END IF;
/*
CREATE TRIGGER CHECK_MUON_BOOK BEFORE INSERT ON MUONTRA FOR EACH ROW 
BEGIN
    DECLARE MSG VARCHAR(100);
    IF(
        EXISTS(
            SELECT MSSV, COUNT(MSSV) 
            FROM MUONTRA 
            WHERE NMUON = CURDATE() 
            GROUP BY MSSV 
            HAVING MSSV = NEW.MSSV AND COUNT(MSSV) >= 3)
    ) THEN
        SET MSG = CONCAT(NEW.MSSV, ' BORROWED 3 BOOKS TODAY'); 
        SIGNAL SQLSTATE '45000' SET  MESSAGE_TEXT = MSG;
    END IF;
DELETE FROM MUONTRA WHERE MSSV = 2021000012 AND BID = 'B043543';
INSERT INTO MUONTRA VALUES ('NV015', 2021000012, 'B043543', '2022-01-20', NULL);
*/

--7 Tạo PROCEDURE tìm kiếm theo mssv
CREATE PROCEDURE SEARCH_BOOK_OF_STUDENT(IN MSSV INT) BEGIN IF(NOT EXISTS(SELECT * FROM STUDENT WHERE STUDENT.MSSV = MSSV)) THEN SELECT 'Sinh viên không tồn tại !!!'; ELSEIF(NOT EXISTS(SELECT * FROM MUONTRA WHERE MUONTRA.MSSV = MSSV AND NTRA IS NULL)) THEN SELECT CONCAT('Sinh viên ', MSSV, ' không có cuốn sách nào đang mượn.') AS 'NOTICE'; ELSE SELECT BNAME, NMUON, HANTRA FROM BOOK, MUONTRA WHERE BOOK.BID = MUONTRA.BID AND NTRA IS NULL AND MUONTRA.MSSV = MSSV; END IF; END #

CALL SEARCH_BOOK_OF_STUDENT(2021188195);
CALL SEARCH_BOOK_OF_STUDENT(2019004705);

--8 PROCEDURE cho hoạt động mượn sách 
CREATE PROCEDURE MUON_SACH(MANV CHAR(5), MSSV INT, BID CHAR(7)) 
BEGIN
    IF(NOT EXISTS(SELECT * FROM STUDENT WHERE STUDENT.MSSV = MSSV)) THEN SELECT CONCAT('Không tồn tại mssv ', MSSV) AS NOTICE;
    ELSEIF(NOT EXISTS(SELECT BOOK.BID FROM BOOK WHERE BOOK.BID = BID)) THEN SELECT CONCAT('Mã ', BID, ' bị sai !!!') AS NOTICE;
    ELSE
        BEGIN
            INSERT INTO MUONTRA(MANV, MSSV, BID, NMUON, NTRA, HANTRA) VALUES(MANV, MSSV, BID, CURDATE(), NULL, NMUON + INTERVAL 4 MONTH);
            UPDATE BOOK SET SL = SL - 1 WHERE BOOK.BID = BID;
        END;
    END IF;
END #  

CALL MUON_SACH('NV015', 2021123456, 'B050000');

--9 Khoa có tỉ lệ sinh viên đọc sách cao nhất
SELECT SVDOCSACH.MAKHOA AS MK, (SVDOCSACH.A*100/TSVMOIKHOA.B) AS TL FROM (SELECT MAKHOA, COUNT(DISTINCT MUONTRA.MSSV) AS A FROM MUONTRA, STUDENT WHERE MUONTRA.MSSV = STUDENT.MSSV GROUP BY MAKHOA) SVDOCSACH, (SELECT MAKHOA, COUNT(MSSV) AS B FROM STUDENT GROUP BY MAKHOA) TSVMOIKHOA WHERE SVDOCSACH.MAKHOA = TSVMOIKHOA.MAKHOA GROUP BY MK HAVING TL >= ALL (SELECT (SVDOCSACH.A*100/TSVMOIKHOA.B) FROM (SELECT MAKHOA, COUNT(DISTINCT MUONTRA.MSSV) AS A FROM MUONTRA, STUDENT WHERE MUONTRA.MSSV = STUDENT.MSSV GROUP BY MAKHOA) SVDOCSACH, (SELECT MAKHOA, COUNT(MSSV) AS B FROM STUDENT GROUP BY MAKHOA) TSVMOIKHOA WHERE SVDOCSACH.MAKHOA = TSVMOIKHOA.MAKHOA GROUP BY MK);

--10 Tác giả có tổng số sách được mượn nhiều nhất và quyển sách được đọc nhiều nhất của tác giả đó cho tới nay
SELECT TACGIA.MATG, TENTG, BNAME, COUNT(MSSV), TG_MAX.C FROM TACGIA, MUONTRA, BOOK, (SELECT MATG, COUNT(MSSV) AS C FROM BTG, BOOK, MUONTRA WHERE BTG.BID = BOOK.BID AND BOOK.BID = MUONTRA.BID GROUP BY MATG HAVING COUNT(MSSV) >= ALL (SELECT COUNT(MSSV) FROM BTG, BOOK, MUONTRA WHERE BTG.BID = BOOK.BID AND BOOK.BID = MUONTRA.BID GROUP BY MATG)) AS TG_MAX, BTG WHERE MUONTRA.BID = BOOK.BID AND BOOK.BID = BTG.BID AND BTG.MATG = TG_MAX.MATG AND TACGIA.MATG = TG_MAX.MATG GROUP BY MUONTRA.BID, TG_MAX.MATG HAVING COUNT(MSSV) >= ALL(SELECT COUNT(MSSV) FROM TACGIA, MUONTRA, BOOK, (SELECT MATG, COUNT(MSSV) AS C FROM BTG, BOOK, MUONTRA WHERE BTG.BID = BOOK.BID AND BOOK.BID = MUONTRA.BID GROUP BY MATG HAVING COUNT(MSSV)>= ALL (SELECT COUNT(MSSV) FROM BTG, BOOK, MUONTRA WHERE BTG.BID = BOOK.BID AND BOOK.BID = MUONTRA.BID GROUP BY MATG)) AS TG_MAX, BTG WHERE MUONTRA.BID = BOOK.BID AND BOOK.BID = BTG.BID AND BTG.MATG = TG_MAX.MATG AND TACGIA.MATG = TG_MAX.MATG GROUP BY MUONTRA.BID);






-- 3 So sánh tỉ lệ trả sách đúng hạn trong 2 năm liên tiếp 
DELIMITER #
CREATE FUNCTION TL_TRA_SACH(YEAR1 INT, YEAR2 INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE MOTA VARCHAR(100);
    IF(NOT EXISTS(SELECT * FROM MUONTRA WHERE YEAR(NMUON) = YEAR1)) THEN SET MOTA = CONCAT('NAM ', YEAR1, ' KHONG TON TAI');
    ELSEIF(NOT EXISTS(SELECT * FROM MUONTRA WHERE YEAR(NMUON) = YEAR2)) THEN SET MOTA = CONCAT('NAM ', YEAR2, ' KHONG TON TAI');
    ELSE
        BEGIN
            DECLARE TL1, TL2 DECIMAL(10,2);
            SET TL1 = (SELECT DUNG_HAN.COUNT_DUNG_HAN * 100 / TONG_TRA FROM (SELECT COUNT(*) AS COUNT_DUNG_HAN FROM MUONTRA WHERE YEAR(NTRA) = YEAR1 AND NTRA <= HANTRA) DUNG_HAN, (SELECT COUNT(*) AS TONG_TRA FROM MUONTRA WHERE YEAR(NTRA) = YEAR1) TONG);

            SET TL2 = (SELECT DUNG_HAN.COUNT_DUNG_HAN * 100 / TONG_TRA FROM (SELECT COUNT(*) AS COUNT_DUNG_HAN FROM MUONTRA WHERE YEAR(NTRA) = YEAR2 AND NTRA <= HANTRA) DUNG_HAN, (SELECT COUNT(*) AS TONG_TRA FROM MUONTRA WHERE YEAR(NTRA) = YEAR2) TONG);
            
            IF TL2 > TL1 THEN 
                SET MOTA = CONCAT('NAM ', YEAR2, '(', TL2,'%)', ' TANG ', ROUND(TL2 - TL1, 2), '%', ' SO VOI NAM ', YEAR1, '(', TL1,'%)');
            ELSEIF TL2 < TL1 THEN 
                SET MOTA = CONCAT('NAM ', YEAR2, '(', TL2,'%)', ' GIAM ', ROUND(TL1 - TL2, 2), '%', ' SO VOI NAM ', YEAR1, '(', TL1,'%)');
            ELSE SET MOTA = 'TI LE KHONG DOI';
            END IF;
        END;
    END IF;
    RETURN MOTA;
END #
DELIMITER ;
RETURN (MOTA);

SELECT TL_TRA_SACH(2020, 2021) AS 'TI LE TRA SACH DUNG HAN';