--group by
select a.dname, max(b.sal)
from dept a, emp b
where
	a.deptno = b.deptno
group by a.dname;

--exceptii
set serveroutput on;
DECLARE
	i number:= 0;
	v_plictiseala exception;
begin
	loop
		i:=i+1;
		dbms_output.put_line('Nr = ' || i);
		if i = 900 then
			raise v_plictiseala;
		end if;
		exit when i = 950;
	end loop;
exception
	when v_plictiseala then
		dbms_output.put_line('Gata');
end;
/

--1 SA se creeze un bloc plsql in care
--sa se citeasca de la tastatura un angajat (numele)
-- si sa se afiseze suma veeniturilor tuturor angajatilor
-- din departamentul managerului sau.
--tratati too many rows, no data found, atentie la king care nu are mgr,

--2 Sa se citeasca de la tast un plafon salarial
--aflati limtele salariale
--cu case:
	--mai putin de doi afiasti 'prea putini'
	--intre 2 si =4 afisati 'destui'
	--5 prea multi
	--mai mult de 5 afiasti o exceptie definita de noi cu medaj

--3 sa se afla cati angajati sunt intr-un dep citit de la tastatura care
--castiga mai mult decat seful lor
--cu exceptii 3


--1
DECLARE
	nume_ang emp.ename%TYPE :='&angajat';
	suma_venituri emp.sal%type;
	manager emp.mgr%type;
	dept_mgr emp.deptno%type;
	v_exc exception;
	pragma exception_init(v_exc,-01400);
BEGIN
	select mgr into manager
	from emp
	where ename = nume_ang;
	DBMS_OUTPUT.PUT_LINE('manager '|| manager);


	SELECT DEPTNO INTO dept_mgr
	FROM EMP
	WHERE empno = manager;
	DBMS_OUTPUT.PUT_LINE('ID_DEPT mgr '|| dept_mgr);


	SELECT sum(sal+nvl(comm,0)) INTO suma_venituri
	FROM EMP
	WHERE DEPTNO = dept_mgr;
	DBMS_OUTPUT.PUT_LINE('suma_venituri: '|| suma_venituri);

	EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NICIUN ANGAJAT');
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('PREA MULTE INREGISTRARI');
	when v_exc then
		dbms_output.put_line('Eroare noua');
END;
/


--2
declare
    plafon salgrade.grade%type:='&plafon';
    min_limit salgrade.losal%type;
    max_limit salgrade.hisal%type;
    nr_angajati number;
    v_too_many exception;
begin
    select losal, hisal into min_limit, max_limit
    from salgrade
    where grade=plafon;

    select count(*) into nr_angajati
    from emp
    where sal >= min_limit and sal <= max_limit;
    dbms_output.put_line('Nr angajati '||nr_angajati);

    case
            when nr_angajati<2 then dbms_output.put_line('Prea putini angajati');
            when nr_angajati>=2 and nr_angajati<=4 then dbms_output.put_line('Destui angajati');
            when nr_angajati=5 then dbms_output.put_line('Prea multi angajati');
            when nr_angajati>5 then raise v_too_many;
        else
            dbms_output.put_line('Numar potrivit');
        end case;
exception
    when v_too_many then
        dbms_output.put_line('Prea multe inregistrari');
end;
/

--3
declare
	nr_angajati number;
	nume_dept dept.dname%type:='&nume_dept';
	id_dept dept.deptno%type;
begin
	select deptno into id_dept
	from dept
	where dname=nume_dept;
	dbms_output.put_line('Departament: '||id_dept);

	select count(*) into nr_angajati
	from emp a join emp b
	on a.mgr=b.empno
	where a.sal>b.sal;
	dbms_output.put_line('Nr ang: '||nr_angajati);

exception
	when no_data_found then
		dbms_output.put_line('Niciun angajat gasit');
	when too_many_rows then
		dbms_output.put_line('Prea multe inregistrari');
	when others then
		dbms_output.put_line('Error');
end;
/