DBMS tsql2

create table employee(
    empno int primary key,
    fname varchar(30),
    sal int,
    tax int
);

insert into employee values(100,'Alethea',75000,0);
insert into employee values(101,'Tamanna',45000,0);
select * from employee

create or replace procedure taxi(eno in int)
as
sal number(9);
taxes int;
begin
    select sal into sal from employee where empno = eno;
    sal := sal * 12;
    if sal <= 700000 then
        taxes := 0;
    elsif sal <= 150000 then
        taxes := (sal - 700000) * 0.1;
    elsif sal <= 200000 then
        taxes := (sal - 700000) * 0.2;
    else
        taxes := (sal - 700000) * 0.3;
    end if;
    update employee set tax = taxes where empno = eno;
end taxi;
/

exec taxi(100)

------------------------------------ Products------------------------------

create table salesreps(empno int primary key, sales_amount number(6))
create table product(pid int primary key, qty int, rate int);
create table orders(orderno int primary key, empno int, pid int, qty_ordered int, amount int)

create or replace trigger t1
after insert
on orders
for each row
begin
update product set qty = qty - :new.qty_ordered
    where pid =: new.pid;
update salesreps set sales_amount = sales_amount + :new.amount where empno =: new.empno;
end t1;
/

insert into product values (200, 50, 200)
insert into product values (201, 30, 500)
insert into product values (202, 20, 800)

insert into salesreps values (10,0)
insert into salesreps values (11,0)

insert into orders values (20, 11, 201, 10, 50000)
truncate table orders
select * from salesreps