13,14,15 oracle

13.	Volem saber quants empleats treballen a cada departament. Mostra de cada departament, el número d'empleats i el nom de departament.

SELECT COUNT(e.employee_id) as NUMERO_TREBALLADORS,d.department_name,d.department_id
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.department_id = d.department_id
GROUP BY  d.DEPARTMENT_NAME, d.department_id;






14.	Partint de la consulta anterior, mostra només els departaments que tenen assignats més de 30 empleats. Ordena la informació segons el número d'empleats.

SELECT COUNT(e.employee_id) as NUMERO_TREBALLADORS,d.department_name,d.department_id
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.department_id = d.department_id
GROUP BY  d.DEPARTMENT_NAME, d.department_id
HAVING COUNT(e.employee_id) > 30;

15.	Mostra els empleats que tenen un sou superior a la mitjana del departament de Vendes (Sales). Volem saber el nom, cognom, salari i nom de departament.

SELECT e.FIRST_NAME, e.LAST_NAME, e.SALARY, d.DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND salary >

                                                    (SELECT AVG(e.SALARY)
                                                    FROM EMPLOYEES e, DEPARTMENTS d
                                                    where e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.department_name = 'Sales');