drop table angajat;
drop table departament;

create table angajat (
	id number,
	nume varchar(15),
	prenume varchar(15),
	id_sef number,
	id_dept number,
	salariu float,
	bonus float
);

insert into angajat values (1, 'Grey', 'Meredith', 2, 1, 500.0, 0);
insert into angajat values (2, 'Webber', 'Richard', 0, 1, 700.0, 0);
insert into angajat values (3, 'Yang', 'Christina', 2, 1, 500.0, 0);

insert into angajat values (4, 'Nu am', 'deptartament', 1, 512, 800.0, 0);

insert into angajat values (5, 'Karamazov', 'Mitia', 0, 2, 800.0, 0);
insert into angajat values (6, 'Karamazov', 'Ivan', 5, 2, 800.0, 0);
insert into angajat values (7, 'Karamazov', 'Alexei', 5, 2, 800.0, 0);

insert into angajat values (8, 'Advertiser', 'Mad', 0, 4, 200.0, 0);
insert into angajat values (9, 'Serbanescu', 'Ilie', 8, 3, 400.0, 0);
insert into angajat values (10, 'Voinea', 'Liviu', 8, 3, 400.0, 0);

SELECT * FROM angajat;

create table departament (
	id_dept number,
	nume varchar(20)
);

insert into departament values (1, 'Medicina');
insert into departament values (2, 'IT');
insert into departament values (3, 'Economie');
insert into departament values (4, 'Marketing');
insert into departament values (256, 'Nu am angajati');

SELECT * FROM departament;

select A.id, A.nume || ',' || A.prenume NumeAngajat, A.salariu, B.nume NumeSef, D.nume, (select avg(C.salariu) 
from angajat C
where (C.id_dept=A.id_dept)
group by C.id_dept) medie
from angajat A, angajat B, departament D
where (A.id_dept=D.id_dept(+) and A.id_sef=B.id(+))
UNION
select null, null, null, null, nume, null
from departament
where (id_dept=256);

--select avg(A.salariu) 
--from angajat A, angajat B, departament D
--where (A.id_dept=D.id_dept and A.id_sef=B.id(+))
--group by A.id_dept;


-- @"C:\Documents and Settings\Student\Desktop\lab5.sql"
