WTzf3tqp

1DROP SCHEMA IF EXISTS SHEET5;
2CREATE SCHEMA IF NOT EXISTS SHEET5;
3
4USE SHEET5;
5
6CREATE TABLE IF NOT EXISTS DEPT (
7	Dnumber VARCHAR(10),
8	Dname VARCHAR(100),
9    Founded DATE,
10    Mgr_ssn VARCHAR(10),
11    Budget INT,
12    CONSTRAINT DEPT_PK PRIMARY KEY(Dnumber)
13);
14
15CREATE TABLE IF NOT EXISTS EMPLOYEE (
16	Ssn VARCHAR(10),
17	Ename VARCHAR(100),
18    Bdate DATE,
19    Dno VARCHAR(10),
20    Salary INT,
21    CONSTRAINT EMP_PK PRIMARY KEY(Ssn),
22    CONSTRAINT EMP_DEPT_FK FOREIGN KEY(Dno) REFERENCES DEPT(Dnumber)
23);
24
25ALTER TABLE DEPT ADD CONSTRAINT DEPT_MGR_FK FOREIGN KEY(Mgr_ssn) REFERENCES EMPLOYEE(Ssn);
26
27## Definitions
28DELIMITER //
29## REQ 1
30CREATE FUNCTION Count_Emp(DNUMBER VARCHAR(10)) RETURNS INT DETERMINISTIC
31BEGIN
32	DECLARE cnt int;
33    SET cnt = 0;
34	SELECT COUNT(*) INTO cnt FROM EMPLOYEE GROUP BY Dno HAVING Dno = DNUMBER;
35	RETURN cnt;
36END//
37## REQ 2
38CREATE PROCEDURE Year()
39BEGIN
40	SET SQL_SAFE_UPDATES = 0;
41    
42	UPDATE DEPT AS UDEPT
43    SET UDEPT.Founded = '1960-1-1'
44    WHERE UDEPT.Dnumber IN (
45		SELECT *
46        FROM (
47			SELECT Dnumber
48            FROM DEPT
49            WHERE FOUNDED < '1960-1-1') AS D);
50
51	SET SQL_SAFE_UPDATES = 1;
52END//
53## REQ 3
54CREATE TRIGGER MAX_EMP BEFORE INSERT ON EMPLOYEE
55FOR EACH ROW
56BEGIN
57	IF (NEW.Dno IN (SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT(*) >= 8))
58    THEN
59		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: A Department Can\'t Have More Than 8 Employees';
60    END IF;
61END//
62## REQ 4
63CREATE TRIGGER UPDATE_DNO_CASCADE AFTER UPDATE ON DEPT
64FOR EACH ROW
65BEGIN
66	SET SQL_SAFE_UPDATES = 0;
67
68	IF (NEW.Dnumber != OLD.Dnumber)
69    THEN
70		UPDATE EMPLOYEE
71        SET Dno = NEW.Dnumber
72        WHERE Dno = OLD.Dnumber;
73    END IF;
74    
75	SET SQL_SAFE_UPDATES = 1;
76END//
77## REQ 5
78CREATE TRIGGER UPDATE_MGR_SALARY BEFORE UPDATE ON EMPLOYEE
79FOR EACH ROW
80BEGIN
81	SET SQL_SAFE_UPDATES = 0;
82    
83		UPDATE EMPLOYEE
84        SET Salary = NEW.Salary
85        WHERE Ssn IN (
86			SELECT Ssn
87            FROM DEPT
88            JOIN EMPLOYEE ON Mgr_ssn = Ssn
89            WHERE Dnumber = NEW.Dno
90            AND Salary < NEW.Salary);
91
92    SET SQL_SAFE_UPDATES = 1;
93END//
94DELIMITER ;
95
96
97INSERT INTO DEPT (Dnumber, Founded) VALUES (1, '1959-1-1'), (2, '1960-1-2');
98INSERT INTO EMPLOYEE(Ssn, Dno, Salary) VALUES(1, 1, 1), (2, 1, 5000), (3, 2, 1000);
99INSERT INTO DEPT (Dnumber, Mgr_Ssn) VALUES (3, 1);
100INSERT INTO EMPLOYEE(Ssn, Dno, Salary) VALUES (4, 3, 2);
101## TEST REQ 3
102#INSERT INTO EMPLOYEE(Ssn, Dno) VALUES(1, 1), (2, 1), (3, 2), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1), (10, 1);
103
104SELECT * FROM DEPT;
105
106## TEST REQ 1
107SELECT Count_Emp(1);
108SELECT Count_Emp(2);
109
110## TEST REQ 2
111CALL Year();
112SELECT * FROM DEPT;
113
114## TEST REQ 4 (THIS WON'T WORK);
115-- SET SQL_SAFE_UPDATES = 0;
116-- UPDATE DEPT SET Dnumber = 4 WHERE Dnumber = 2;
117-- SET SQL_SAFE_UPDATES = 1;
118-- SELECT * FROM EMPLOYEE;
119
120## TEST REQ 5 (THIS WON'T WORK);
121-- SET SQL_SAFE_UPDATES = 0;
122-- UPDATE EMPLOYEE SET Salary = 10000 WHERE Ssn = 4;
123-- SET SQL_SAFE_UPDATES = 1;
124-- SELECT * FROM EMPLOYEE;