DEV Community

Vaniusca 23
Vaniusca 23

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

--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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

END;`

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay