How to make use of outer joins and rollup in oracle sql

--1.Sa se afiseze first_name managerului, department_name si city pentru toate departamentele din baza de date. In cazul in care un departament nu are manager, se va afisa "Fara manager" in campul first_name

select coalesce (e.first_name, 'Fara manager') as manager_name, d.department_name, l.city
from hr.departments d
left join  hr.employees e on d.manager_id = e.employee_id
join hr.locations  on d.location_id = l.location_id;

--2.Folosind “joncțiunea externă” sa se afișeze numele departamentelor care nu au angajați.

select department_name from hr.departments d
full outer join hr.employees e on d.department_id = e.department_id
where e.employee_id is null;

–2’.(extra)8.Folosind "jonctiunea externa" (1pct) sa se afiseze numele departamentelor care nu au angajati in "JOB_HISTORY". (5)
select d.department_name
from hr.departments d
left outer join hr.job_history jh
on d.department_id = jh.department_id
where jh.department_id is null -- Nu a avut niciun angajat care să fi părăsit departamentul
and d.department_id in (select distinct department_id from hr.employees);

--3.Folosind ROLLUP(1pct), reuniuni(2pct) sa se afiseze departamentele, managerii și numarul de angajati afisandu-se subtotaluri la nivel de manageri, departamente și total general.

`–rollup
select
decode (grouping(d.department_id),
1, 'total department',
nvl(to_char(d.department_id), ‘fara departament’)) as department_id,
decode(grouping(e.manager_id),
1, 'subtotal manager',
to_char(e.manager_id)) as manager_id,
count (*) as nr_angajati
from hr.employees e
left join hr.departments d on e.department_id = d.department_id
group by rollup (d.department_id, e.manager_id)
order by department_id nulls last, manager_id nulls last;

–reuniuni
select nvl(to_char(d.department_id), 'fara departament') as department_id,
to_char(e.manager_id) as manager_id,
count (*) as nr_angajati
from hr.employees e
left join hr.departments d on e.department_id = d.department_id
group by d.department_id, e.manager_id

union all

select nvl (to_char(d.department_id), 'fara departament') as department_id,
null as manager_id,
count (*) as nr_angajati
from hr.employees e
left join hr.departments d on e.department_id = d.department_id
group by d.department_id

union all

select'total departament' as department_id,
null as manager_id,
count (*) as nr_angajati
from hr.employees
order by department_id nulls last, manager_id nulls last;

–combinat
select
decode(grouping_id(d.department_id, e.manager_id), 0, nvl(to_char(d.department_id), 'fara departament'),
1, 'subtotal manager',

2, 'total departament',
3, 'total general' )
as department_id, decode(grouping_id(d.department_id, e.manager_id), 0,
to_char (e.manager_id), 1, null, 2, null, 3, null ) as manager_id, count (*) as nr_angajati
from hr.employees e
left join hr.departments d on e.department_id = d.department_id
group by grouping sets ((d.department_id, e.manager_id), (d.department_id), ())
order by department_id nulls last, manager_id nulls last;`

--4.Folosind “start with” sa se afiseze subordonatii subordonatilor angajatului 109.

select employee_id, first_name, last_name
from hr.employees
where level = 2
start with manager_id = 109
connect by prior employee_id = manager_id;

--5.Sa se creeze o procedura denumita SUMA, care sa afiseze suma salariilor tuturor angajatilor.

create or replace PROCEDURE SUMA AS
v_total_salary NUMBER;
BEGIN
SELECT SUM(salary) INTO v_total_salary FROM hr.employees;
DBMS_OUTPUT.PUT_LINE('Suma salariilor tuturor angajatilor este: ' || v_total_salary);
END SUMA;

--6.Sa se creeze o procedura care sa afiseze cati angajati sunt intr-un departament. Departamentul va fi introdus la executie ca și parametru al procedurii.

`create or replace PROCEDURE numar_angajati_departament (
p_department_name IN hr.departments.department_name%TYPE
) IS
v_numar_angajati NUMBER;
BEGIN
-- Selectăm numărul de angajați din departamentul specificat
SELECT COUNT(employee_id)
INTO v_numar_angajati
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
WHERE d.department_name = p_department_name;

-- Afișăm rezultatul
DBMS_OUTPUT.PUT_LINE('Numarul de angajati in departamentul ' || p_department_name || ' este: ' || v_numar_angajati);

END;`

--7.Sa se creeze o functie ca un parametru care sa returneze cate orase din bază de date sunt din US. Acest numar va fi afisat după linia cu apelul functiei.

`--functia propriu zisa
create or replace FUNCTION numar_orase_sua
RETURN NUMBER IS
v_numar_orase NUMBER;
BEGIN
-- Selectăm numărul de orașe unde country_id este 'US'
SELECT COUNT(DISTINCT city)
INTO v_numar_orase
FROM hr.locations
WHERE country_id = 'US';

-- Returnăm valoarea
RETURN v_numar_orase;

END;

--rezultat
SET SERVEROUTPUT ON;
DECLARE
v_total_orase NUMBER;
BEGIN
-- Apelăm funcția și stocăm rezultatul
v_total_orase := numar_orase_sua;

-- Afișăm rezultatul
DBMS_OUTPUT.PUT_LINE('Numarul de orase din SUA este: ' || v_total_orase);

END;`