assi7

CREATE TABLE department
(
    departmentid VARCHAR(4) primary key,
    dname VARCHAR(20)
);
CREATE TABLE employee
(
    employeeid VARCHAR(4) primary key,
    ename VARCHAR(20),
    salary NUMBER,
    departmentid VARCHAR(4),
    constraints fk_did  foreign key (departmentid) references department(departmentid) ON DELETE cascade
);
CREATE TABLE empraise
(
    empraiseid VARCHAR(4) primary key,
    employeeid VARCHAR(4),
    raise_date DATE,
    raise_amount NUMBER,
    constraints fk_eid  foreign key (employeeid) references employee(employeeid) ON DELETE cascade
);

--insert

INSERT INTO department VALUES ('d01','JAVA');
INSERT INTO department VALUES ('d02','HTML');
INSERT INTO department VALUES ('d03','C++');
INSERT INTO department VALUES ('d04','BOOTSTRAP4');
INSERT INTO department VALUES ('d05','PHP');

--insert
INSERT INTO employee VALUES('e01','Aadesh',35000,'d01');
INSERT INTO employee VALUES('e02','Brijal',45000,'d02');
INSERT INTO employee VALUES('e03','Rahul',55000,'d03');
INSERT INTO employee VALUES('e04','Hardik',45600,'d04');
INSERT INTO employee VALUES('e05','Lucky',26000,'d05');
INSERT INTO employee VALUES('e06','Devanshi',66000,'d01');
INSERT INTO employee VALUES('e07','Chirag',24000,'d02');
INSERT INTO employee VALUES('e08','Pratik',40000,'d03');
INSERT INTO employee VALUES('e09','Sunny',44000,'d04');
INSERT INTO employee VALUES('e10','Kajal',17000,'d05');
INSERT INTO employee VALUES('e11','Fahad',91000,'d01');
INSERT INTO employee VALUES('e12','Aniket',89000,'d02');
INSERT INTO employee VALUES('e13','Darshan',79000,'d03');
INSERT INTO employee VALUES('e14','Ankit',57000,'d04');

2)
--sequance;
CREATE sequence sw
START WITH 1
increment BY 1;
--trigger

CREATE OR REPLACE TRIGGER trg_ere
after UPDATE ON employee
BEGIN
    IF updating THEN
        INSERT INTO empraise (empraiseid)VALUES('er'||sw.NEXTVAL);
    END IF;
END trg_ere;
/

    --insert into empraise values('er'||sw.nextval,employeeid,sysdate,3000);
UPDATE employee
SET salary=salary+(salary*0.2)
WHERE departmentid =(SELECT departmentid FROM department WHERE dname='JAVA');