lab 4 bd rezolvat

--lab 4
--ex2
SELECT
    MIN(salary) AS "Minim",
    MAX(salary) AS "Maxim",
    SUM(salary) AS "Suma",
    round(AVG(salary)) AS "Media"
FROM
    employees;

--ex3
SELECT job_id, MIN(salary), MAX(salary), SUM(salary), avg(salary)
FROM employees
GROUP BY job_id;

--ex4
SELECT job_id, COUNT(employee_id)
FROM employees
GROUP BY job_id;

--ex5
SELECT COUNT(DISTINCT manager_id) AS "Nr. manageri"
FROM employees;

--ex6
SELECT MAX(salary) - MIN(salary) AS "Diferenta"
FROM employees;

--ex7
SELECT department_name, city, COUNT(employee_id), avg(salary)
FROM employees emp
JOIN departments dep USING (department_id)
JOIN locations loc USING (location_id)
GROUP BY department_name, city;

--ex8
SELECT employee_id, last_name
FROM employees
WHERE salary > (
                SELECT avg(salary)
                FROM employees
                )
ORDER BY salary DESC;

--ex9
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id, salary
HAVING MIN(salary) > 1000
ORDER BY 2 DESC;

--ex10
SELECT department_id, department_name, MAX(salary)
FROM departments dep JOIN employees emp USING (department_id)
GROUP BY department_id, department_name
HAVING MAX(salary) > 3000;

--ex11
SELECT round(avg(MIN(salary)))
FROM employees
GROUP BY job_id;

 --ex12
 SELECT department_id, department_name, SUM(salary)
 FROM departments dep JOIN employees emp USING (department_id)
 GROUP BY department_id, department_name;

 --ex13
 SELECT round(MAX(avg(salary)))
 FROM employees
 GROUP BY department_id;

 --ex14
 SELECT job_id, job_title, avg(salary)
 FROM jobs JOIN employees emp USING (job_id)
 GROUP BY job_id, job_title
 HAVING avg(salary) = (SELECT MIN(avg(salary))
                       FROM employees
                       GROUP BY job_id);

--ex 16
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id;

--ex 18a
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;

--ex 18b
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);


--ex 20 v2
SELECT COUNT(COUNT(department_id))
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 15;

--ex 21
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id != 30
GROUP BY department_id
HAVING COUNT(employee_id) > 10
ORDER BY SUM(salary);

--ex 22
SELECT department_id, department_name, COUNT(employee_id), avg(salary), last_name, salary, job_id
FROM departments dep JOIN employees emp USING (department_id)
GROUP BY department_id, department_name;

--ex23
SELECT city, department_name, job_id, SUM(salary)
FROM employees emp JOIN departments dep USING (department_id)
JOIN locations loc USING (location_id)
WHERE department_id > 80
GROUP BY department_name, job_id, city;

--ex24
SELECT employee_id
FROM job_history
GROUP BY employee_id
HAVING COUNT(employee_id) >= 2;

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

--ex28
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';