dbms

create database dbms;
use dbms;

drop table sv;

CREATE TABLE SV (
Masv CHAR(10) PRIMARY KEY,
Hoten VARCHAR(100) NOT NULL,
Ngaysinh DATE NOT NULL,
Nganh VARCHAR(50) NOT NULL,
DTB FLOAT,
DRL FLOAT,
Xeploai VARCHAR(20)
);

select * from SV;

INSERT INTO SV (Masv, Hoten, Ngaysinh, Nganh, DTB, DRL, Xeploai) VALUES
('SV001', 'Nguyen Van A', '2002-05-10', 'CNTT', 8.5, 90, NULL),
('SV002', 'Tran Thi B', '2001-07-15', 'Kinh Te', 7.2, 85, NULL),
('SV003', 'Le Van C', '2003-02-20', 'Y Duoc', 6.8, 70, NULL),
('SV004', 'Pham Thi D', '2002-11-25', 'CNTT', 9.0, 95, NULL),
('SV005', 'Hoang Van E', '2001-04-05', 'Co Khi', 7.5, 80, NULL),
('SV006', 'Vu Thi F', '2003-09-30', 'CNTT', 8.0, 88, NULL),
('SV007', 'Nguyen Van G', '2002-06-12', 'Dien Tu', 6.5, 75, NULL),
('SV008', 'Bui Thi H', '2001-08-23', 'Kinh Te', 9.2, 92, NULL),
('SV009', 'Dang Van I', '2003-12-14', 'Y Duoc', 5.8, 65, NULL),
('SV010', 'Tran Van K', '2002-01-07', 'CNTT', 8.7, 91, NULL),
('SV011', 'Ngo Thi L', '2001-10-16', 'Co Khi', 7.9, 82, NULL),
('SV012', 'Le Van M', '2003-03-27', 'Dien Tu', 6.2, 73, NULL),
('SV013', 'Hoang Thi N', '2002-07-19', 'Y Duoc', 7.8, 87, NULL),
('SV014', 'Pham Van O', '2001-09-29', 'CNTT', 9.5, 97, NULL),
('SV015', 'Nguyen Thi P', '2003-11-05', 'Kinh Te', 8.1, 89, NULL);

drop function F_Xeploai;

DELIMITER $$
CREATE FUNCTION F_Xeploai (diem_tb FLOAT, diem_rl FLOAT) RETURNS VARCHAR(20) deterministic
BEGIN
DECLARE xepLoai VARCHAR(20);
IF diem_tb >= 9 THEN SET xepLoai = 'Xuat Sac';
ELSEIF diem_tb >= 8 THEN SET xepLoai = 'Gioi';
ELSEIF diem_tb >= 7 THEN SET xepLoai = 'Kha';
ELSEIF diem_tb >= 5 THEN SET xepLoai = 'Trung Binh';
ELSE SET xepLoai = 'Yeu';
END IF;

    IF diem_rl < 70 AND xepLoai <> 'Yeu' THEN
		CASE xepLoai
			WHEN 'Xuat Sac' THEN SET xepLoai = 'Gioi';
			WHEN 'Gioi' THEN SET xepLoai = 'Kha';
			WHEN 'Kha' THEN SET xepLoai = 'Trung Binh';
			WHEN 'Trung Binh' THEN SET xepLoai = 'Yeu';
		END CASE;
	END IF;
    
    RETURN xepLoai;
END; $$

DELIMITER ;

SET SQL_SAFE_UPDATES = 0;

UPDATE SV SET Xeploai = F_Xeploai(DTB, DRL);

drop table Tiendien;

CREATE TABLE Tiendien (
MaKH CHAR(10) PRIMARY KEY,
Hoten VARCHAR(100) NOT NULL,
Diachi VARCHAR(255) NOT NULL,
Thang INT,
Sodien INT,
Thanhtien INT
);

INSERT INTO Tiendien (MaKH, Hoten, Diachi, Thang, Sodien, Thanhtien) VALUES
('KH001', 'Nguyen Van A', 'Ha Noi', 1, 45, NULL),
('KH002', 'Tran Thi B', 'Hai Phong', 1, 250, NULL),
('KH003', 'Le Van C', 'Da Nang', 1, 120, NULL),
('KH004', 'Pham Thi D', 'Ho Chi Minh', 1, 250, NULL),
('KH005', 'Hoang Van E', 'Can Tho', 1, 30, NULL),
('KH006', 'Vu Thi F', 'Bac Giang', 2, 95, NULL),
('KH007', 'Nguyen Van G', 'Nam Dinh', 2, 60, NULL),
('KH008', 'Bui Thi H', 'Quang Ninh', 2, 110, NULL),
('KH009', 'Dang Van I', 'Thanh Hoa', 2, 200, NULL),
('KH010', 'Tran Van K', 'Nghe An', 2, 40, NULL),
('KH011', 'Ngo Thi L', 'Binh Dinh', 3, 75, NULL),
('KH012', 'Le Van M', 'Quang Ngai', 3, 150, NULL),
('KH013', 'Hoang Thi N', 'Hue', 3, 90, NULL),
('KH014', 'Pham Van O', 'Phu Yen', 3, 210, NULL),
('KH015', 'Nguyen Thi P', 'Binh Thuan', 3, 130, NULL),
('KH016', 'Nguyen Van Q', 'Khanh Hoa', 4, 55, NULL),
('KH017', 'Tran Thi R', 'Dak Lak', 4, 170, NULL),
('KH018', 'Le Van S', 'Gia Lai', 4, 190, NULL),
('KH019', 'Pham Thi T', 'Kon Tum', 4, 20, NULL),
('KH020', 'Hoang Van U', 'Vinh Long', 4, 250, NULL);

DELIMITER $CREATE FUNCTION F_Tiendien (Sodien INT) RETURNS INT DETERMINISTIC BEGIN DECLARE total INT; IF Sodien >= 1 AND Sodien <= 50 THEN SET total = Sodien * 1500; ELSEIF Sodien > 50 AND Sodien <= 100 THEN SET total = 50 * 1500 + (Sodien - 50) * 2000; ELSEIF Sodien > 100 AND Sodien <= 200 THEN SET total = 50 * 1500 + 50 * 2000 + (Sodien - 100) * 2500; ELSE SET total = 50 * 1500 + 50 * 2000 + 100 * 2500 + (Sodien - 200) * 3000; END IF; RETURN total; END;$
DELIMITER ;

set sql_safe_updates = 0;

DELIMITER $CREATE PROCEDURE P_Tiendien() BEGIN SET SQL_SAFE_UPDATES = 0; UPDATE Tiendien SET Thanhtien = F_Tiendien(Sodien); END;$
DELIMITER ;

CALL P_Tiendien;

select * from Tiendien;

show triggers;

DELIMITER $$

DELIMITER $$

select count(MaKH) from Tiendien;

drop procedure count_cursor;

CREATE PROCEDURE count_cursor()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE total_rows INT DEFAULT 0;
DECLARE user_name VARCHAR(100);

-- Khai báo cursor
DECLARE cur CURSOR FOR SELECT Hoten FROM Tiendien;

-- Bắt lỗi NOT FOUND
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- Mở cursor
OPEN cur;

-- Lặp qua các hàng
read_loop: LOOP
    FETCH cur INTO user_name;
    IF done THEN
        LEAVE read_loop;
    END IF;
    
    -- Đếm số dòng
    SET total_rows = total_rows + 1;
END LOOP;

-- Đóng cursor
CLOSE cur;

-- Hiển thị tổng số dòng
SELECT total_rows AS total_cursor_rows;

END$$
DELIMITER ;

CALL count_cursor();