--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;`
Top comments (0)