c6e9xBZp

1CREATE SCHEMA IF NOT EXISTS `174exam1`;
2
3USE `174exam1`;
4
5#1
6CREATE TABLE IF NOT EXISTS Department(
7	dnumber 	int, 
8    dname 		varchar(20), 
9    location 	varchar(20), 
10    noOfEmp 	int,
11    PRIMARY KEY(dnumber)
12);
13
14CREATE TABLE IF NOT EXISTS Employee(
15	ssn			int,
16    name		varchar(20),
17    address		varchar(20),
18	phone		int(9),
19    dno			int,
20    PRIMARY KEY(ssn),
21    FOREIGN KEY(dno) REFERENCES Department(dnumber)
22);
23
24TRUNCATE Department;
25TRUNCATE Employee;
26
27INSERT INTO Department
28VALUES (01, 'Biology', 'Sequoia', 21);
29
30INSERT INTO Department
31VALUES (02, 'Computer Science', 'Riverside', 11);
32
33INSERT INTO Employee
34VALUES (123456789, 'Eric', '1 Address', 123456789, 02);
35
36INSERT INTO Employee
37VALUES (111111111, 'Janet', '2 Address', 111111111, 01);
38
39INSERT INTO Employee
40VALUES (987654321, 'Coach', '3 Address', 987654321, 02);
41
42#Test #2
43INSERT INTO Employee
44VALUES (222222222, 'Diane', '4 Address', 222222222, 1);
45
46#Test #3
47DELETE FROM Employee 
48WHERE ssn = 222222222;
49
50#Test #4
51UPDATE Employee
52SET dno = 2
53WHERE ssn = 222222222;
54
55SELECT * from Department;
56
57UPDATE Department 
58SET noOfEmp = 0
59WHERE dnumber = 2;
60
61#2 WHY WONT THIS WORK
62DELIMITER $
63CREATE TRIGGER ins_t
64	AFTER INSERT
65    ON Employee
66    FOR EACH ROW
67    BEGIN
68		UPDATE Department, Employee
69        SET noOfEmp = noOfEmp + 1
70        WHERE dnumber = dno;
71	END $
72DELIMITER ;
73
74#3
75DELIMITER $
76CREATE TRIGGER del_t
77	AFTER DELETE
78    ON Employee
79    FOR EACH ROW
80    BEGIN
81		UPDATE Department, Employee
82        SET noOfEmp = noOfEmp - 1
83        WHERE Department.dnumber = Employee.dno;
84	END $
85DELIMITER ;
86
87#4
88DELIMITER $
89CREATE TRIGGER upd_t
90	AFTER UPDATE
91    ON Employee
92    FOR EACH ROW
93    BEGIN
94		UPDATE Department, Employee
95        SET noOfEmp = noOfEmp
96        WHERE Department.dnumber = Employee.dno;
97	END $
98DELIMITER ;