wyhV1wxF

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', 0);
29 
30INSERT INTO Department
31VALUES (02, 'Computer Science', 'Riverside', 0);
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
43/*
44INSERT INTO Employee
45VALUES (222222222, 'Diane', '4 Address', 222222222, 1);
46
47INSERT INTO Employee
48VALUES (123890213, 'ASDF', '5 ASDFA', 198392834, 1);
49*/
50
51SELECT * from Department;
52 
53#Test #3
54DELETE FROM Employee
55WHERE ssn = 222222222;
56 
57#Test #4
58UPDATE Employee
59SET dno = 2
60WHERE ssn = 222222222;
61 
62#UPDATE Department
63#SET noOfEmp = 0
64#WHERE dnumber = 1;
65 
66#2
67DELIMITER $
68CREATE TRIGGER ins_t
69    AFTER INSERT
70    ON Employee
71    FOR EACH ROW
72    BEGIN
73        UPDATE Department, Employee
74        SET noOfEmp = noOfEmp + 1
75        WHERE dnumber = dno;
76    END $
77DELIMITER ;
78
79DROP TRIGGER ins_t;
80
81
82
83SELECT * FROM Department;
84SHOW TRIGGERS;
85DROP TRIGGER ins_t;
86
87#3
88DELIMITER $
89CREATE TRIGGER del_t
90    AFTER DELETE
91    ON Employee
92    FOR EACH ROW
93    BEGIN
94        UPDATE Department, Employee
95        SET noOfEmp = noOfEmp - 1
96        WHERE dnumber = dno;
97    END $
98DELIMITER ;
99 
100/*
101#4
102DELIMITER $
103CREATE TRIGGER upd_t
104    AFTER UPDATE
105    ON Employee
106    FOR EACH ROW
107    BEGIN
108        UPDATE Department, Employee
109        SET noOfEmp = noOfEmp
110        WHERE Department.dnumber = Employee.dno;
111    END $
112DELIMITER ;*/