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;`

Image of AssemblyAI tool

Transforming Interviews into Publishable Stories with AssemblyAI

Insightview is a modern web application that streamlines the interview workflow for journalists. By leveraging AssemblyAI's LeMUR and Universal-2 technology, it transforms raw interview recordings into structured, actionable content, dramatically reducing the time from recording to publication.

Key Features:
🎥 Audio/video file upload with real-time preview
🗣️ Advanced transcription with speaker identification
⭐ Automatic highlight extraction of key moments
✍️ AI-powered article draft generation
📤 Export interview's subtitles in VTT format

Read full post

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

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay