07

use 20200198db;
set foreign_key_checks = 0;
drop table if exists employee, dept, department, deptSalary;
drop procedure if exists updateSalary;
set foreign_key_checks  = 1;

create table dept(
  dnumber integer,
  dname varchar(20),

  primary key(dnumber)
);

insert into dept values
(1, "Payroll"),
(2, "TechSupport"),
(3, "Research");

create table employee(
  id integer,
  name varchar(20),
  superId integer,
  salary float(10, 4),
  bdate date,
  dno integer,

  primary key(id),
  foreign key(dno) references dept(dnumber)
);

insert into employee values
(1, "john", 3, 100000, "1960-01-01", 1),
(2, "mary", 3, 50000, "1964-12-01", 3),
(3, "bob", null, 80000, "1974-02-07", 3),
(4, "tom", 1, 50000, "1978-01-17", 2),
(5, "bill", null, null, "1985-01-20", 1);

create table deptSalary as
select dnumber, 0 as totalSalary from dept;

delimiter \\
create procedure updateSalary(IN paraml int)
begin
    update deptSalary
    set totalSalary = (
        select sum(salary) from employee where dno = paraml
    )
    where dnumber = paraml;
end; \\
delimiter ;
call updateSalary(1);
call updateSalary(2);
call updateSalary(3);

select * from deptSalary;

select * from employee;
drop function if exists giveRaise;
delimiter \\
create function giveRaise(oldval double, amount double)
returns double
deterministic
begin
    declare newval double;
    set newval = oldval * (1+amount);
    return newval;
end \\
delimiter ;

select name, salary, giveRaise(salary, 0.1) as newsal from employee;