faSAcSMf

1Drop schema if exists final ;
2create  schema final ;
3Use final;
4Set sql_safe_updates = 0 ;
5
6
7create table DEPT (
8	Dnum		int primary key not null,
9    Dname		varchar(40) not null,
10    Founded		date not null,
11    Mgr_ssn		varchar(15) not null,
12    Budget		int not null);
13    
14create table EMPLOYEE (
15	Ssn			varchar(15) primary key not null,
16    Ename 		varchar(40) not null,
17    Bdate		date not null,
18    Dno			int ,
19    Salary		int not null ,
20foreign key (Dno) references DEPT(Dnum));
21
22
23insert into DEPT values (1, 'Main department', '1990-01-01', '111-222-333', 500000);
24insert into DEPT values (2, 'Production', '1950-05-01', '222-333-444', 100000);
25insert into DEPT values (3, 'Human recources', '2005-10-01', '333-444-555', 20000);
26insert into DEPT values (4, 'Marketing', '1920-11-01', '444-555-666', 40000);
27
28
29insert into EMPLOYEE values ('111-222-333', 'Alberto Giubilini', '1991-05-21', 1, 120000);
30insert into EMPLOYEE values ('123-456-789', 'Anders Sandberg', '1995-06-05', 1, 90000);
31insert into EMPLOYEE values ('147-258-369', 'Bill Fulford', '1997-06-18', 1, 85000);
32insert into EMPLOYEE values ('741-852-963', 'Christopher Gyngell', '1990-07-22', 1, 91000);
33insert into EMPLOYEE values ('147-852-369', 'Derek Bolton', '1998-12-10', 1, 5000);
34insert into EMPLOYEE values ('222-333-222', 'Rocci Wilkinson', '1985-01-12', 1, 20000);
35
36insert into EMPLOYEE values ('222-333-444', 'John Tasioulas', '1995-10-15', 2, 150000);
37insert into EMPLOYEE values ('963-852-741', 'Jonathan Pugh', '1993-02-15', 2, 120000);
38insert into EMPLOYEE values ('963-741-258', 'Mark Sheehan', '1995-04-15', 2, 8000);
39
40DELIMITER //
41
42CREATE  FUNCTION count_Emp(D int ) RETURNS int(11)
43    DETERMINISTIC
44BEGIN
45    declare countEmp int ;
46    set countEmp = (select count(SSN) from final.EMPLOYEE where D=Dnum);
47  
48RETURN countEmp;
49END
50//
51
52
53
54DELIMITER //
55
56CREATE DEFINER=`root`@`localhost` PROCEDURE `pro`()
57BEGIN
58
59	update  DEPT set founded  = '01-JAN-1960'  where Year(founded) >= '1960' ; 
60	
61
62END
63//
64
65
66Delimiter //
67create trigger dept_insert 
68before insert  ON DEPT
69for each row 
70Begin 
71
72 IF (count_Emp(DEPT.dno) > 8 ) THEN
73         call error ;
74      END IF;
75End 
76//
77
78
79
80Delimiter //
81create trigger update_Dept 
82after update  ON DEPT 
83for each row
84Begin 
85
86update EMPLOYEE as E
87set E.Dno = NEW.Dnum 
88where E.Dno = OLD.Dnum;
89End
90//
91-- select count_Emp (1);
92Delimiter //
93create trigger after_update 
94after update  ON EMPLOYEE 
95for each row
96Begin 
97if (New.salary > OLD.Salary) -- get relation between emp & manager
98Then 
99
100	update EMPLOYEE as E, DEPT as D
101	set E.Salary =E.Salary+ NEW.Salary -OLD.Salary
102	where E.ssn = D.Mgr_ssn and E.Dno = D.Dnum;
103end if ;
104End
105//
106
107
108select * from Dept;
109select * from Employee;
110update employee set salary = salary + 2 where dno = 1;