Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- //* 1. Mostrar el nombre de cada departamento con la cantidad de empleados que tiene. */
- SELECT dept.department_name, COUNT(employee_id) FROM employees emp
- INNER JOIN departments dept ON emp.department_id = dept.department_id
- GROUP BY dept.department_name;
- /*2. Mostrar el nombre completo de cada jefe con la cantidad de empleados que tiene a su cargo.*/
- SELECT man.first_name ||' '|| man.last_name AS Full_name, COUNT(emp.employee_id) FROM employees emp
- INNER JOIN employees man ON emp.manager_id = man.employee_id
- GROUP BY man.employee_id, man.first_name, man.last_name
- ORDER BY COUNT(emp.employee_id) DESC;
- /*3. Mostrar el nombre de cada departamento, la cantidad de empleados que posee por cargo.*/
- SELECT dept.department_name, jb.job_title, COUNT(employee_id) FROM employees emp
- INNER JOIN departments dept ON emp.department_id = dept.department_id
- INNER JOIN jobs jb ON emp.job_id = jb.job_id
- GROUP BY dept.department_name, jb.job_title
- ORDER BY 1, 2 ASC;
- /*4. Mostrar de cada departamento, cuánto paga por salarios, el sueldo promedio, el sueldo más alto y el sueldo más bajo.*/
- SELECT dept.department_name, SUM(emp.salary), AVG(emp.salary), MAX(emp.salary), MIN(emp.salary) FROM employees emp
- INNER JOIN departments dept ON emp.department_id = dept.department_id
- GROUP BY dept.department_name
- ORDER BY 4 DESC;
- /*5. Mostrar el nombre de los empleados y la cantidad de cargos que han tenido, según el historia de cargos registrado.
- select count(jo.job_id),emp.employee_id,jo.job_title,emp.first_name
- from job_history jh inner join jobs jo on jo.job_id=jh.job_id inner join employees emp on jo.job_id=emp.job_id
- group by jh.job_id,emp.employee_id, jo.job_title,emp.first_name
- order by 1 desc;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement