# 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;