Realización de consultas.Consultas III

TABLA EMPLE

TABLA DEPART.

TABLA HERRAMIENTAS

TABLA PERSONAS

TABLA MEDICOS

TABLA HOSPITALES

1. Visualizar el número de empleados de cada departamento. Utilizar GROUP BY para agrupar por departamento.

select emple.dept_no,count(*)

from emple,depart

where emple.dept_no=depart.dept_no

group by emple.dept_no;

2. Visualizar los departamentos con más de 5 empleados. Utilizar GROUP BY para agrupar por departamento y HAVING para establecer la condición sobre los grupos.

select emple.dept_no,count(*)

from emple,depart

where emple.dept_no=depart.dept_no

group by emple.dept_no

having count(*)>5;

3. Hallar la media de los salarios de cada departamento (utilizar la función avg y GROUP BY).

select emple.dept_no,avg(salario)

from emple,depart

where emple.dept_no=depart.dept_no

group by emple.dept_no;

4. Visualizar el nombre de los empleados vendedores del departamento ʻVENTASʼ (Nombre del departamento=ʼVENTASʼ, oficio=ʼVENDEDORʼ).

select apellido

from emple,depart

where emple.dept_no=depart.dept_no and dnombre='VENTAS' and oficio='VENDEDOR';

5. Visualizar el número de vendedores del departamento ʻVENTASʼ (utilizar la función COUNT sobre la consulta anterior).

select count(*)

from emple,depart

where emple.dept_no=depart.dept_no and dnombre='VENTAS' and oficio='VENDEDOR';

6. Visualizar los oficios de los empleados del departamento ʻVENTASʼ.

select apellido,oficio

from emple,depart

where emple.dept_no=depart.dept_no and dnombre='VENTAS';

7. A partir de la tabla EMPLE, visualizar el número de empleados de cada departamento cuyo oficio sea ʻEMPLEADOʼ (utilizar GROUP BY para agrupar por departamento. En la cláusula WHERE habrá que indicar que el oficio es ʻEMPLEADOʼ).

select emple.dept_no,count(*)

from emple,depart

where emple.dept_no=depart.dept_no and oficio='EMPLEADO'

group by emple.dept_no;

8. Visualizar el departamento con más empleados.

select dept_no

from emple

group by count(*)

having count(*)=(select

max(count(*))

from emple

group by dept_no);

9. Mostrar los departamentos cuya suma de salarios sea mayor que la media de salarios de todos los empleados.

select dept_no,avg(salario)

from emple

group by dept_no

having avg(salario)>(select avg(salario)

from emple);

10. Para cada oficio obtener la suma de salarios.

select oficio,sum(salario)

from emple

group by oficio;

11. Visualizar la suma de salarios de cada oficio del departamento ʻVENTASʼ.

select oficio,sum(salario)

from emple,depart

where emple.dept_no=depart.dept_no and dnombre='VENTAS'

group by oficio;

12. Visualizar el número de departamento que tenga más empleados cuyo oficio sea empleado.

select dept_no

from emple

where oficio='empleado'

group by dept_no

having count(*)=(select max(count(*)) from emple where oficio='empleado' group by dept_no);

13. Mostrar el número de oficios distintos de cada departamento.

select dept_no,count(*)

from emple

group by dept_no,oficio;

14. Mostrar los departamentos que tengan más de dos personas trabajando en la misma profesión.

select dept_no,count(*)

from emple

group by dept_no,oficio

having count(*)>2;

15. Dada la tabla HERRAMIENTAS, visualizar por cada estantería la suma de las unidades.

Estantería SUMA

-

1

---------------

25

2 7

3 17

4 10

5 15

6 15

select estanteria,sum(unidades)

from herramientas

group by estanteria;

16. Visualizar la estantería con más unidades de la tabla HERRAMIENTAS. Estantería - 1

select estanteria,sum(unidades)

from herramientas

group by estanteria

having sum(unidades)=(select max(sum(unidades))

from herramientas

group by estanteria);

Tablas PERSONAS, MEDICOS, HOSPITALES.

17. Mostrar el número de médicos que pertenecen a cada hospital, ordenado por número descendente de hospital.

select hospitales.cod_hospital,count(*)

from hospitales, medicos

where hospitales.cod_hospital=medicos.cod_hospital

group by hospitales.cod_hospital;

18. Realizar una consulta en la que se muestre por cada hospital el nombre de las especialidades que tiene.

select nombre,especialidad,medicos.cod_hospital

from hospitales,medicos

where hospitales.cod_hospital=medicos.cod_hospital;

19. Realizar una consulta en la que aparezca por cada hospital y en cada especialidad el número de médicos (tendrás que partir de la consulta anterior y utilizar GROUP BY).

select nombre,especialidad,count(*)

from hospitales,medicos

where hospitales.cod_hospital=medicos.cod_hospital

group by nombre,especialidad;

20. Obtener por cada hospital el número de empleados.

select hospitales.cod_hospital,nombre,count(*)

from personas,hospitales

where personas.cod_hospital=hospitales.cod_hospital

group by personas.cod_hospital;

21. Obtener por cada especialidad el número de trabajadores.

select especialidad,count(*)

from medicos

group by especialidad;

22. Visualizar la especialidad que tenga más médicos.

select espescialidad

from medicos

group by especialidad

having count(*)=(select max(count(*))

from medicos

group by especialidad);

23. ¿Cuál es el nombre del hospital que tiene mayor número de plazas?

select

cod_hospital,nombre

from hospitales

where

num_plazas=(select max(num_plazas) from hospitales);

24. Visualizar las diferentes estanterías de la tabla HERRAMIENTAS ordenados descendentemente por estantería.

select distinct(estanteria)

from herramientas

order by estanteria desc;

25. Averiguar cuántas unidades tiene cada estantería.

select estanteria,sum(unidades)

from herramientas

group by estanteria;

26. Visualizar las estanterías que tengan más de 15 unidades

select estanteria

from herramientas

group by estanteria

having sum(unidades)>20;

27. ¿Cuál es la estantería que tiene más unidades?

select estanteria

from herramientas

group by estanteria

having sum(unidades)=(select max(sum(unidades)) from herramientas

group by estanteria);

28. A partir de las tablas EMPLE y DEPART mostrar los datos del departamento que no tiene ningún empleado.

select dnombre,dept_no

from depart

where depart.dept_no not in (select dept_no from emple);

29. Mostrar el número de empleados de cada departamento. En la salida se debe mostrar también los departamentos que no tienen ningún empleado.

select depart.dept_no,count(emple.dept_no)

from depart LEFT JOIN emple ON emple.dept_no=depart.dept_no

group by depart.dept_no;

30. Obtener la suma de salarios de cada departamento, mostrando las columnas DEPT_NO, SUMA DE SALARIOS y DNOMBRE. En el resultado también se deben mostrar los departamentos que no tienen asignados Empleados.

select depart.dept_no,IFNULL(sum(salario),'0.0') "Suma salario"

from emple RIGHT JOIN depart ON emple.dept_no=depart.dept_no

group by depart.dept_no;

31. Utilizar la función IFNULL en la consulta anterior para que en el caso de que un departamento no tenga empleados, aparezca como suma de salarios el valor 0.

select depart.dept_no,IFNULL(sum(salario),'0.0') "Suma salario"

from emple RIGHT JOIN depart ON emple.dept_no=depart.dept_no

group by depart.dept_no;

32. Obtener el número de médicos que pertenecen a cada hospital, mostrando las columnas COD_HOSPITAL, NOMBRE y NÚMERO DE MÉDICOS. En el resultado deben aparecer también los datos de los hospitales que no tienen médicos.

select hospitales.cod_hospital,nombre,count(medicos.dni)

from medicos RIGHT JOIN hospitales ON hospitales.cod_hospital=medicos.cod_hospital

group by hospitales.cod_hospital;