nhom25

# Bai tap on tap nhom KHDL

# Vu Quang Manh - Nhom truong
# Tran Thi Nhu Quynh
# Vu Hoang Long
# Nguyen Dinh Minh
# 4 thanh vien tham gia day du



# TAO CSDL
DROP DATABASE IF EXISTS qlnv;

CREATE DATABASE qlnv CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

USE qlnv;

CREATE TABLE IF NOT EXISTS NHANVIEN (
    MA_NV INT,
    TEN_NV VARCHAR(15),
    TEN_CONG_VIEC VARCHAR(10),
    MA_QUAN_LY INT,
    NGAY_THUE DATE,
    LUONG FLOAT(10,2),
    TIEN_HOA_HONG FLOAT(7,2),
    MA_PHONG INT,
    PRIMARY KEY(MA_NV)
) ENGINE=INNODB;

INSERT INTO NHANVIEN(MA_NV, TEN_NV, TEN_CONG_VIEC, MA_QUAN_LY, NGAY_THUE, LUONG, TIEN_HOA_HONG, MA_PHONG) VALUES 
    (63679, 'SANDRINE', 'CLERK', 65646, '1990-12-18', 1400, NULL, 2001),
    (64989, 'ADELYN', 'SALESMAN', 66928, '1991-02-20', 1700, 400, 3001),
    (65271, 'WADE', 'SALESMAN', 66928, '1991-02-22', 1350, 600, 3001),
    (65646, 'JONAS', 'MANAGER', 68319, '1991-04-02', 2957, NULL, 2001),
    (66928, 'BLAZE', 'MANAGER', 68319, '1991-05-01', 2750, NULL, 3001),
    (67832, 'CLARE', 'MANAGER', 68319, '1991-06-09', 2550, NULL, 1001),
    (67858, 'SCARLET', 'ANALYST', 65646, '1997-04-19', 3100, NULL, 2001),
    (68319, 'KAYLING', 'PRESIDENT', NULL, '1991-11-18', 6000, NULL, 1001),
    (69000, 'JULIUS', 'CLERK', 66928, '1991-12-03', 1050, NULL, 3001),
    (69324, 'MARKER', 'CLERK', 67832, '1992-01-23', 1400, NULL, 1001)
;

CREATE TABLE IF NOT EXISTS PHONGBAN (
    MA_PHONG INT,
    TEN_PHONG VARCHAR(20),
    DIA_DIEM_PHONG VARCHAR(15),
    SO_NV INT,
    PRIMARY KEY(MA_PHONG)
) ENGINE=INNODB;

INSERT INTO PHONGBAN VALUES 
    (1001, 'FINANCE' , 'SYDNEY' , 3),
    (2001,'AUDIT','MELBOURNE',3),
    (3001,'MARKETING','PERTH',4),
    (4001,'PRODUCTION','BRISBANE',0);

CREATE TABLE IF NOT EXISTS BACLUONG  (
    MA_BAC_LUONG INT,
    MIN_LUONG FLOAT(10,2),
    MAX_LUONG FLOAT(10,2),
    PRIMARY KEY(MA_BAC_LUONG)
) ENGINE=INNODB;

INSERT INTO BACLUONG VALUES 
    (1,'800','1300'),
    (2,'1301','1500'),
    (3,'1501','2100'),
    (4,'2101','3100'),
    (5,'3101','9999');

ALTER TABLE NHANVIEN ADD FOREIGN KEY (MA_PHONG) REFERENCES phongban(MA_PHONG);

ALTER TABLE NHANVIEN ADD FOREIGN KEY (MA_QUAN_LY) REFERENCES nhanvien(MA_NV);



# CAU 1
DELIMITER $$
DROP FUNCTION IF EXISTS cau1 $$
CREATE FUNCTION cau1(manv INT)
RETURNS VARCHAR(50)
BEGIN
    DECLARE tenql VARCHAR(50);
    SELECT QL.TEN_NV 
    INTO tenql
    FROM NHANVIEN NV 
    JOIN NHANVIEN QL
    ON NV.MA_QUAN_LY = QL.MA_NV
    WHERE NV.MA_NV = manv;
    RETURN tenql;
END $$
DELIMITER ;;

SELECT cau1(63679);



# CAU 2
DELIMITER $$
DROP FUNCTION IF EXISTS cau2 $$
CREATE FUNCTION cau2(manv int) 
RETURNS VARCHAR(50)
BEGIN 
	DECLARE tenphg VARCHAR(50);
	SELECT phongban.TEN_PHONG 
    INTO tenphg 
    FROM phongban 
    join nhanvien 
    on phongban.MA_PHONG = nhanvien.MA_PHONG 
    WHERE nhanvien.MA_NV = manv ;
	RETURN tenphg;
END $$
DELIMITER ;;

SELECT cau2(63679);


# CAU 3
DELIMITER $$
DROP FUNCTION IF EXISTS cau3 $$
CREATE FUNCTION cau3(maphg int) 
RETURNS varchar(50)
BEGIN 
	DECLARE tennv varchar(50);
	SELECT nhanvien.TEN_NV 
    INTO tennv 
    FROM nhanvien 
    WHERE nhanvien.MA_PHONG = maphg 
    AND nhanvien.LUONG = (
        SELECT MAX(nhanvien.LUONG) 
        FROM nhanvien 
        WHERE nhanvien.MA_PHONG = maphg);
	RETURN tennv;
END $$
DELIMITER ;;

SELECT cau3(3001);


# CAU 4
DELIMITER $$
DROP FUNCTION IF EXISTS cau4 $$
CREATE FUNCTION cau4(maphg int) 
RETURNS varchar(50)
BEGIN 
	DECLARE tennv varchar(50);
	SELECT nhanvien.TEN_NV 
    INTO tennv 
    FROM nhanvien 
    WHERE nhanvien.MA_PHONG = maphg 
    AND nhanvien.LUONG = (
        SELECT MIN(nhanvien.LUONG) 
        FROM nhanvien 
        WHERE nhanvien.MA_PHONG = maphg
    );
	RETURN tennv;
END $$
DELIMITER ;;

SELECT cau4(3001);


# CAU 5
DELIMITER $$
DROP FUNCTION IF EXISTS cau5 $$
CREATE FUNCTION cau5(manv int) 
RETURNS decimal(10,2)
BEGIN 
	DECLARE bl decimal(10,2) DEFAULT -1;
	DECLARE luongnv decimal(10,2);
    
	SELECT nhanvien.LUONG 
    INTO luongnv
    FROM nhanvien 
    WHERE nhanvien.MA_NV = manv;
    
    SELECT bacluong.MA_BAC_LUONG 
    INTO bl
    FROM bacluong 
    WHERE bacluong.MIN_LUONG <= luongnv 
    AND bacluong.MAX_LUONG >= luongnv;
    
	RETURN bl;
END $$
DELIMITER ;;

SELECT cau5(63679);


# CAU 6
DELIMITER $$
DROP TRIGGER IF EXISTS cau6 $$
CREATE TRIGGER cau6
AFTER INSERT 
ON NHANVIEN FOR EACH ROW
BEGIN
    UPDATE PHONGBAN
    SET SO_NV = SO_NV + 1
    WHERE NEW.MA_PHONG = PHONGBAN.MA_PHONG;
END $$
DELIMITER ;;


# CAU 7


# CAU 8
DELIMITER $$
DROP TRIGGER IF EXISTS cau8 $$
CREATE TRIGGER cau8 
BEFORE INSERT 
ON NHANVIEN FOR EACH ROW
BEGIN
	SET NEW.TEN_NV = TRIM(NEW.TEN_NV);
	SET NEW.TEN_CONG_VIEC = UPPER(NEW.TEN_CONG_VIEC);
END $$
DELIMITER ;;


# CAU 9
DELIMITER $$
DROP TRIGGER IF EXISTS cau9 $$
CREATE TRIGGER cau9 
BEFORE INSERT 
ON NHANVIEN FOR EACH ROW
BEGIN
	IF NEW.LUONG < (
        SELECT BACLUONG.MIN_LUONG 
        FROM BACLUONG
        WHERE BACLUONG.MA_BAC_LUONG <= ALL (
            SELECT BACLUONG.MA_BAC_LUONG 
            FROM BACLUONG
        )
    )
	THEN
    	SET NEW.LUONG = (
            SELECT BACLUONG.MIN_LUONG 
            FROM BACLUONG
            WHERE BACLUONG.MA_BAC_LUONG <= ALL(
                SELECT BACLUONG.MA_BAC_LUONG 
                FROM BACLUONG
            )
        );
    END IF;
END $$
DELIMITER ;;


# CAU 10
DELIMITER $$
DROP TRIGGER IF EXISTS cau10 $$
CREATE TRIGGER cau10 
BEFORE INSERT 
ON NHANVIEN FOR EACH ROW
BEGIN
    IF NEW.LUONG > (
        SELECT BACLUONG.MAX_LUONG 
        FROM BACLUONG
        WHERE BACLUONG.MA_BAC_LUONG >= ALL (
            SELECT BACLUONG.MA_BAC_LUONG 
            FROM BACLUONG
        )
    )
	THEN
    	SET NEW.LUONG = (
            SELECT BACLUONG.MAX_LUONG 
            FROM BACLUONG
            WHERE BACLUONG.MA_BAC_LUONG >= ALL (
            SELECT BACLUONG.MA_BAC_LUONG 
            FROM BACLUONG
        )
    );
    END IF;
END $$
DELIMITER ;


SHOW TRIGGERS;