CREATE TABLE Departament(
ID number(2) PRIMARY KEY,
Nume varchar(12) UNIQUE,
ID_Sef number(2)
);

CREATE TABLE Angajat(
ID number(2) PRIMARY KEY,
Nume varchar(10) UNIQUE,
Prenume varchar(10) UNIQUE,
Data date,
Salariu number(5) Constraint Salariu_Check CHECK (Salariu <= 9900 AND Salariu >= 500),
ID_Dept number(2) REFERENCES Departament(ID)
);

INSERT INTO Departament VALUES (1, 'Development', 1);
INSERT INTO Departament VALUES (2, 'Research', 3);
INSERT INTO Departament VALUES (3, 'Sales', 5);
INSERT INTO Departament VALUES (4, 'Marketing', 7);
INSERT INTO Departament VALUES (5, 'HR', 9);

INSERT INTO Angajat VALUES (1, 'Tapus' ,'Radu', to_date('1-07-2010','dd-mm-yyyy'), 1900,  1);
INSERT INTO Angajat VALUES (2, 'Fotea' ,'Ioana', to_date('5-01-2011','dd-mm-yyyy'), 1400, 1);
INSERT INTO Angajat VALUES (3, 'Neacsu' ,'Mihai', to_date('8-07-2009','dd-mm-yyyy'), 1887,  2);
INSERT INTO Angajat VALUES (4, 'Badescu' ,'Ana', to_date('5-08-2011','dd-mm-yyyy'), 843,  2);
INSERT INTO Angajat VALUES (5, 'Popescu' ,'Ion', to_date('6-07-2012','dd-mm-yyyy'), 1534, 3);
INSERT INTO Angajat VALUES (6, 'Ciuculete' ,'Maria', to_date('15-07-2009','dd-mm-yyyy'), 838, 3);
INSERT INTO Angajat VALUES (7, 'Ion' ,'Vasile', to_date('2-07-2011','dd-mm-yyyy'), 1915, 4);
INSERT INTO Angajat VALUES (8, 'Moldoveanu' ,'Viorel', to_date('1-07-2010','dd-mm-yyyy'), 643, 4);
INSERT INTO Angajat VALUES (9, 'Cornel' ,'Gheorghe', to_date('1-07-2011','dd-mm-yyyy'), 756, 5);
INSERT INTO Angajat VALUES (10, 'Mamaliga' ,'Alexandru', to_date('1-07-2012','dd-mm-yyyy'), 685, 5);

Select * From Angajat;
Select * From Departament;

Alter Table Angajat
Add Bonus number(4);

Update Angajat
Set Bonus = Salariu / 10;

Select * From Angajat;
Select * From Departament;

Update Angajat a
Set Bonus = Bonus * 2
Where (a.Salariu + a.Bonus) < 
((Select Salariu From Angajat s1 Where s1.ID = (Select ID_Sef From Departament d1 Where a.ID_Dept = d1.ID))
+
(Select Bonus From Angajat s2 Where s2.ID = (Select ID_Sef From Departament d2 Where a.ID_Dept = d2.ID))
) / 2;

Select * From Angajat;
Select * From Departament;

CREATE TABLE Raport AS
Select concat(concat(a.Nume, ' '), a.Prenume) NumePrenume,
a.Data Data_Ang,
a.Salariu,
a.Salariu + a.Bonus Venit,
d.Nume Departament,
concat(concat(b.Nume, ' '), b.Prenume) Sef,
b.Salariu + b.Bonus Venit_Sef
From Angajat a, Angajat b, Departament d
Where a.ID_Dept = d.ID AND b.ID = (Select d1.ID_Sef From Departament d1 Where a.ID_Dept = d1.ID AND d1.ID = d.ID);

Select * From Raport;

SET ECHO OFF;
SET PAGESIZE 50;

TTITLE CENTER UNDERLINE 'Raport';

COL A FOR A20 HEA 'Nume Angajat';
COL B HEA 'Data_Ang';
COL C FOR 9999 HEA 'Salariu';
COL D FOR 99999 HEA 'Venit';
COL E FOR A12 HEA 'Departament';
COL F FOR A18 HEA 'Nume Sef';
COL G FOR 9999 HEA 'Venit Sef';

BREAK ON E NODUP ON REPORT;
COMPUTE SUM LABEL 'Venit Departament' OF D ON E;
COMPUTE SUM LABEL 'Total:' OF D ON REPORT;

Select r.NumePrenume A, r.Data_Ang B, r.Salariu C, r.Venit D, r.Departament E, r.Sef F, r.Venit_Sef G
From Raport r
Order By E; 

TTITLE OFF;
CLEAR COLUMNS;
CLEAR COMPUTE;

DROP TABLE Angajat;
DROP TABLE Departament;
DROP TABLE Raport;
