create table male_emp as (
select * from employee where sex=
);

create or replace TRIGGER count_num_employees
BEFORE INSERT ON employee
FOR EACH ROW
DECLARE
num_emp number(5);
BEGIN
SELECT count(*) into num_emp from employee where dno=:new.dno;
DBMS_OUTPUT.PUT_LINE('Employees in department ' || :new.dno || ' before insert:' || num_emp);
END;
/

create table action_log (
table_name varchar(50),
action varchar(50),
time_of_action date
);

create or replace TRIGGER audit_insert_employee
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
INSERT INTO action_log VALUES ('EMPLOYEE', 'INSERT', SYSDATE);
END;
/

commit;

insert into EMPLOYEE values('John','A','Doe',2323232323,'1984-03-10','291 Berry Bellaire TX','M',55000,null,1);

DECLARE
sal employee.salary%type;
emp_num number(3);
BEGIN
SELECT count(*) INTO emp_num FROM employee;
SELECT sum(salary) INTO sal FROM employee;
sal:=sal+10000;
DBMS_OUTPUT.PUT_LINE(sal);
END;
/

create or replace function bmi (height int , weight float) return float is
BEGIN
if height <= 0.3 or height > 3.0 then
dbms_output.put_line ('height must be in [0.3, 3.0] meters') ;
end if;
if weight <= 0 then
dbms_output.put_line('weight must be positive');
elsif weight > 800 then
dbms_output.put_line('Check the specified value is correct');
end if;
return weight/(height*height); --Μπορώ να χρησιμοποιήσω και pl/sql functions π.χ. power(height,2)
END;

create or replace function get_bonus(salary float) return float is
bonus employee.salary%type;
BEGIN
if salary> 35000 then
bonus := salary*0.1;
else
bonus := salary*0.2;
end if;
return bonus;
END;