nhom25
unknown
sql
3 years ago
5.9 kB
6
Indexable
Never
# 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;