cU45z0Hp

1Drop schema if exists lab_5 ;
2create  schema lab_5 ;
3Use lab_5;
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
22Set foreign_key_checks = 0;
23
24insert into DEPT values (1, 'Main department', '1990-01-01', '111-222-333', 800000);
25insert into DEPT values (2, 'Production', '1950-05-01', '222-333-444', 10000);
26insert into DEPT values (3, 'Human recources', '2005-10-01', '333-444-555', 2000);
27insert into DEPT values (4, 'Marketing', '1920-11-01', '444-555-666', 4000);
28
29
30insert into EMPLOYEE values ('111-222-333', 'Alberto Giubilini', '1991-05-21', 1, 120000);
31insert into EMPLOYEE values ('123-456-789', 'Anders Sandberg', '1995-06-05', 1, 90000);
32insert into EMPLOYEE values ('147-258-369', 'Bill Fulford', '1997-06-18', 1, 85000);
33insert into EMPLOYEE values ('741-852-963', 'Christopher Gyngell', '1990-07-22', 1, 91000);
34insert into EMPLOYEE values ('147-852-369', 'Derek Bolton', '1998-12-10', 1, 5000);
35insert into EMPLOYEE values ('222-333-222', 'Rocci Wilkinson', '1985-01-12', 1, 20000);
36
37insert into EMPLOYEE values ('222-333-444', 'John Tasioulas', '1995-10-15', 2, 150000);
38insert into EMPLOYEE values ('963-852-741', 'Jonathan Pugh', '1993-02-15', 2, 120000);
39insert into EMPLOYEE values ('963-741-258', 'Mark Sheehan', '1995-04-15', 2, 8000);
40
41Set foreign_key_checks = 1;
42
43DELIMITER //
44-- returns the number of employs in the dep given dep no 
45CREATE  FUNCTION count_Emp(D int ) RETURNS int(11)
46    DETERMINISTIC
47BEGIN
48    declare countEmp int ;
49    set countEmp = (select count(SSN) from EMPLOYEE where D=Dnum);
50  
51RETURN countEmp;
52END
53//
54
55
56
57DELIMITER //
58
59CREATE PROCEDURE `pro`()
60BEGIN
61-- UPDATE founded date of department
62	update  DEPT set founded  = '01-JAN-1960'  where Year(founded) < '1960' ; 
63END
64//
65
66
67Delimiter //
68-- NO of emplyee does not exceed 8, if more do not insert it
69create trigger dept_insert 
70before insert  ON DEPT
71for each row 
72Begin 
73 
74 IF (count_Emp(DEPT.dno) > 8 ) THEN
75         call error ;
76      END IF;
77End 
78//
79
80
81
82
83call pro();
84
85Delimiter //
86create trigger update_Dept 
87after update  ON DEPT 
88for each row
89Begin 
90
91update EMPLOYEE as E
92set E.Dno = NEW.Dnum 
93where E.Dno = OLD.Dnum;
94End
95//
96
97CREATE PROCEDURE `updateMngrSalary`(emp_ssn int(200),newsalary int(200))
98BEGIN
99DECLARE manager int(200);
100DECLARE mngrSalary int(200);
101set manager = (select mgr_ssn from dept join employee on dnumber=dno where employee.ssn=emp_ssn);
102set mngrSalary= (select salary from employee where ssn=manager);
103if mngrSalary< newsalary
104then
105update employee set salary=mngrsalary where ssn=manager;
106end if;
107
108END