Tarea5bases

Universidad Autónoma
de Chihuahua
Facultad de Ingeniería
Ingeniería en Software

Tarea 5 SQL Joins II

Materia: Bases de datos
Alumno: Erik David Zubia Hernández.
Matricula: 282071

--Consulta 1Obtener el no. de movimientos de personal por región(region_name, no. movimientos) –
cambio de puesto o departamento
select r.region_name, count(j.department_id)
from regions r join COUNTRIESusing(region_id)
join LOCATIONS using(COUNTRY_ID) join
departments d using (location_id) join job_history j on(d.department_id =
j.department_id)
group by r.region_name;

--Consulta 2
Obtener del departamento‘Sales’, el job_id, job_title , el salario mínimo y máximo de los
puestos de dicho departamento
select jh.job_id, job_title, max_salary, min_salary
from jobs join job_history jhon(jobs.job_id=jh.job_id)
join departments d on (d.department_id=jh.DEPARTMENT_ID)
where lower(d.DEPARTMENT_NAME) like 'sales';

--Consulta 3
Obtener de cada región la nómina promedio(región_name, nomina_promedio)select region_name,avg(salary)
from regions r join COUNTRIES using(REGION_ID)
join locations using(country_id)
join DEPARTMENTS using(location_id)
join employees using(department_id)
group byr.REGION_NAME;

--Consulta 4
Obtener el department_name de los departamentos que tienen una nómina mayor que el
promedio de la nómina del departamento de ‘Human Resources’
select department_name, sum(salary)from DEPARTMENTS join employees using(department_id)
group by department_name
having sum(salary)> (select avg(salary)
from DEPARTMENTS join employees using(department_id)
group by department_namehaving lower(department_name) like 'human resources');

--Consulta 5
Obtener el department_id, department_name, no. de empleados , nómina total, nómina
promedio, salario mayor y menor para cadadepartamento
select department_id, department_name,count(employee_id) numEmp,sum(salary)
nomina,
avg(salary) Promedio, max(salary), min(salary)
from DEPARTMENTS join employees using(department_id)
group by...