Test laborator BD1

Subiect 1

Sa se afiseze in ordinea datei de angajare, crescator, cei mai vechi 10 angajati din departamentul care are cei mai multi angajati cu data de angajare inainte de o data introdusa de la tastatura. Se vor afisa coloanele: Numele complet, Departamentul si Vechimea (exprimata in luni).

select * from (select e.first_name||' '||last_name "Nume complet",d.department_name "Departament",round(months_between(sysdate,e.hire_date)) "Vechime" 
from hr.employees e, HR.departments d,(select * from (select department_id,count(*) ct  from hr.employees ex where ex.hire_date<'&&threshold' group by department_id order by ct desc) where rownum=1) g
where 
  d.department_id=e.department_id and
  d.department_id=g.department_id
  order by e.hire_date asc) where rownum<11

Subiect 2

Sa se afiseze, pentru fiecare departament, cei mai bine platiti 5 angajati, in ordine descrescatoare a salariului. De asemenea, sa se afiseze o linie de sumar dupa fiecare departament, cu suma salariilor celor 5 angajati din top. Coloanele afisate sunt Denumire Departament, Nume Complet Angajat, Salariu

COLUMN CD HEADING 'Departament'
COLUMN NUM HEADING 'Nume angajat'
COLUMN SAL HEADING 'Salariu'
BREAK ON CD SKIP 1
COMPUTE AVG OF SAL ON CD
SELECT D.DEPARTMENT_NAME CD , E.FIRST_NAME||' '||E.LAST_NAME NUM, E.SALARY SAL
FROM HR.DEPARTMENTS D, HR.EMPLOYEES E
WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID
  AND (SELECT COUNT(*) FROM HR.EMPLOYEES EX WHERE EX.SALARY>E.SALARY AND EX.DEPARTMENT_ID=E.DEPARTMENT_ID)<5
ORDER BY CD ASC, SAL DESC

Subiect 3

Sa se afiseze suma salariilor din fiecare departament, ordonate dupa numarul de sefi din departamentul respectiv. Se vor afisa coloanele: Denumire Departament, Suma Salarii, Numar Sefi, Numarul de persoane care nu sunt Sefi.

SELECT D.DEPARTMENT_NAME "Nume departament", G.SAL "Total salarii", H.CT "Numar sefi", L.CT "Numar non-sefi"
FROM HR.DEPARTMENTS D,
  (SELECT DEPARTMENT_ID, SUM(EX.SALARY) SAL FROM HR.EMPLOYEES EX GROUP BY EX.DEPARTMENT_ID) G,
  (SELECT DEPARTMENT_ID, COUNT(*) CT FROM HR.EMPLOYEES EXX WHERE EXISTS(SELECT * FROM HR.EMPLOYEES EXXX WHERE EXXX.MANAGER_ID=EXX.EMPLOYEE_ID) GROUP BY EXX.DEPARTMENT_ID) H,
  (SELECT DEPARTMENT_ID, COUNT(*) CT FROM HR.EMPLOYEES EXX WHERE NOT EXISTS(SELECT * FROM HR.EMPLOYEES EXXX WHERE EXXX.MANAGER_ID=EXX.EMPLOYEE_ID) GROUP BY EXX.DEPARTMENT_ID) L
WHERE D.DEPARTMENT_ID=G.DEPARTMENT_ID
  AND D.DEPARTMENT_ID=H.DEPARTMENT_ID
  AND D.DEPARTMENT_ID=L.DEPARTMENT_ID

Subiect 4

Pentru un anumit job introdus de la tastatura, sa se afiseze, pentru fiecare departament in care e prezent, Denumirea departamentului, Numarul de salariati cu jobul respectiv, Salariul total, Salariul mediu, Data angajarii celui mai vechi angajat si Vechimea medie (exprimata in luni).

Subiect 5

Sa se afiseze cei mai bine platiti 3 angajati din fiecare departament si cei mai prost platiti 3 angajati, in ordine descrescatoare ( salariu mediu pe departament, salariu). Coloanele afisate sunt: Nume departament, Nume complet angajat, Salariu, Diferenta fata de salariul mediu pe departament (+/-) Sa se afiseze de asemenea o linie de sumar cu salariul mediu pe departamentul respectiv.

SELECT D.DEPARTMENT_NAME "Nume departament",E.FIRST_NAME||' '||E.LAST_NAME "Nume angajat",E.salary "Salariu",E.salary-(select avg(exx.salary) from hr.employees exx where exx.department_id=e.department_id) "Diferenta"
FROM HR.DEPARTMENTS D, HR.EMPLOYEES E
WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID AND 
  ((SELECT COUNT (*) FROM HR.EMPLOYEES EX WHERE EX.SALARY > E.SALARY AND EX.DEPARTMENT_ID=E.DEPARTMENT_ID)<3 OR 
    (SELECT COUNT(*) FROM HR.EMPLOYEES EX WHERE EX.SALARY < E.SALARY AND EX.DEPARTMENT_ID=E.DEPARTMENT_ID)<3)
order by e.department_id, e.salary desc