tema sapt 6

1. Sa se afiseze codul, numele departamentului si numarul de angajati care lucreaza in acel departament pentru:
 a) departamentele in care lucreaza mai putin de 4 angajati;
 b) departamentul care are numarul maxim de angajati.
a)
SELECT department_id, department_name, COUNT(employee_id)
FROM departments dep JOIN employees emp USING (department_id)
GROUP BY department_id, department_name
HAVING COUNT(employee_id) < 4;
b)
SELECT department_id, department_name, COUNT(employee_id)
FROM departments dep JOIN employees emp USING (department_id)
GROUP BY department_id, department_name
HAVING COUNT(employee_id) = ( SELECT MAX(COUNT(employee_id))
                                FROM employees
                                GROUP BY department_id);

2. Sa se afiseze salariatii care au fost angajati în aceeaşi zi a lunii în care cei mai multi dintre salariati au fost angajati.

SELECT employee_id, last_name, TO_CHAR(hire_date, ‘dd’)
FROM employees
WHERE TO_CHAR(hire_date,'dd') IN
(SELECT TO_CHAR(hire_date,'dd')
FROM employees
GROUP BY TO_CHAR(hire_date,'dd')
HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM employees
GROUP BY TO_CHAR(hire_date,'dd')));

3. Sa se afiseze numarul departamentelor care au cel puţin 15 angajaţi.

SELECT COUNT(COUNT(department_id))
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 15;

4. Să se calculeze comisionul mediu din firmă, luând în considerare toate liniile din tabel.

SELECT avg(nvl(commission_pct,0))
FROM employees;

5. Scrieţi o cerere pentru a afişa job-ul, salariul total pentru job-ul respectiv pe departamente si salariul total pentru job-ul respectiv pe departamentele 30, 50, 80. Se vor eticheta coloanele corespunzător. Rezultatul va apărea sub forma de mai jos (rezolvati in 2 variante DECODE + subcereri in SELECT):
 Job         Dep30          Dep50           Dep80            Total

Varianta 1:

SELECT job_id, SUM(DECODE(department_id, 30, salary)) Dep30,
        SUM(DECODE(department_id, 50, salary)) Dep50,
        SUM(DECODE(department_id, 80, salary)) Dep80,
        SUM(salary) Total
FROM employees
GROUP BY job_id;

Varianta 2:

SELECT job_id, (SELECT SUM(salary)
FROM employees
WHERE department_id = 30
AND job_id = e.job_id) Dep30,
(SELECT SUM(salary)
FROM employees
WHERE department_id = 50
AND job_id = e.job_id) Dep50,
(SELECT SUM(salary)
FROM employees
WHERE department_id = 80
AND job_id = e.job_id) Dep80,
SUM(salary) Total
FROM employees e
GROUP BY job_id;

6. Să se creeze o cerere prin care să se afişeze numărul total de angajaţi şi, din acest total, numărul celor care au fost angajaţi în 1997, 1998, 1999 si 2000. Denumiti capetele de tabel in mod corespunzator. (folosind subcereri in clauza SELECT)

SELECT COUNT(employee_id),
            (SELECT COUNT(employee_id)
            FROM employees
            WHERE to_char(hire_date, 'yyyy') = 1997) AS "1997"
            ,
            (SELECT COUNT(employee_id)
            FROM employees
            WHERE to_char(hire_date, 'yyyy') = 1998) AS "1998"
            ,
            (SELECT COUNT(employee_id)
            FROM employees
            WHERE to_char(hire_date, 'yyyy') = 1999) AS "1999"
FROM employees
GROUP BY 'orice';

7. Folosind subcereri in clauza FROM, să se afişeze codul, numele departamentului şi suma salariilor pe departamente.

SELECT d.department_id, department_name,a.suma
FROM departments d, (SELECT department_id ,SUM(salary) suma
FROM employees
GROUP BY department_id) a
WHERE d.department_id =a.department_id;

8. Pentru fiecare departament, să se afişeze numele acestuia, numele şi salariul celor mai prost plătiţi angajaţi din cadrul său (folosind subcereri in clauza FROM).

--(se cer 3 solutii: subcerere sincronizata, subcerere nesincronizata si subcerere în clauza FROM).
--1--corelata
SELECT last_name,salary,department_id
FROM employees e
WHERE salary = (SELECT min(salary) FROM employees WHERE department_id=e.department_id);

--2--in from
SELECT last_name,salary,e.department_id
FROM employees e join
     (SELECT min(salary) salariu,department_id FROM employees GROUP BY department_id ) a
      on (e.department_id=a.department_id)
WHERE e.salary=a.salariu;

--3--nesincronizata
SELECT last_name,salary,department_id
FROM employees
WHERE (department_id,salary) IN (SELECT department_id,min(salary) FROM employees GROUP BY department_id);