CREATE TABLE Angajat 
(
ID int,
Nume varchar(15),
Prenume varchar(15),
ID_Sef int,
ID_Dept int,
Salariu int,
Bonus int
);
CREATE TABLE Departament
(
ID int,
Nume varchar(15)
);

Insert Into Angajat (ID, Nume, Prenume, ID_Dept, Salariu, Bonus)
Values (0, 'Tapus', 'Radu', 0, 9999,  0);
Insert Into Angajat Values (1, 'Fotea', 'Ioana', 0,  0,  100,  0);
Insert Into Angajat Values (2, 'Neacsu', 'Mihai', 0, 0, 101, 0);
Insert Into Angajat Values (3, 'Mutuleanu', 'Carol', 0, 1, 102, 0);
Insert Into Angajat Values (4, 'Parvulescu', 'Jennyfer', 3, 1, 100, 0);
Insert Into Angajat (ID, Nume, Prenume, ID_Sef, Salariu, Bonus)
Values (5, 'Stroe',  'Alin', 1,  5,  0);
Insert Into Angajat Values (6, 'Badescu', 'Ana', 2, 2, 100, 0);
Insert Into Angajat Values (7, 'Alba', 'Jessica', 0, 2, 200, 0);
Insert Into Angajat Values (8, 'Gogoasa', 'Costel', 8, 3, 85, 0);
Insert Into Angajat Values (9, 'Craciun', 'Mos', 4, 3, 90, 0);
Insert Into Angajat Values (10, 'Popescu', 'Costel', 9, 4, 90, 0);

Insert Into Departament Values (0, 'Conducere');
Insert Into Departament Values (1, 'IT');
Insert Into Departament Values (2, 'Ajutoare');
Insert Into Departament Values (3, 'HR');
Insert Into Departament Values (4, 'Vanzari');
Insert Into Departament Values (5, 'Degeaba');

Select * From Angajat;
Select * From Departament;

SELECT a.ID , concat (concat (a.Nume , ', '), a.Prenume) Nume, a.Salariu, b.nume nume_sef, c.nume Departament, 
(SELECT avg(d.salariu) FROM Angajat d WHERE a.ID_Dept = d.ID_Dept GROUP BY d.ID_Dept) SalariuMediu
FROM Angajat a, Angajat b, Departament c 
WHERE a.ID_Sef = b.ID(+) AND a.ID_Dept = c.ID(+)
ORDER BY a.Nume;

DROP TABLE Angajat;
DROP TABLE Departament;