Bases de datos. Consultas SQL (II)

Hemos realizado la hoja de ejercicios 5 de la unidad 3, en la que usamos la misma base de datos que en la anterior entrada. Recuerda que la puedes descargar de aquí

EJERCICIOS

CONSULTAS CON AGRUPAMIENTO Y FUNCIONES DE GRUPO

Tablas utilizadas: EMPLEADOS , DEPARTAMENTOS, PEDIDOS, PRODUCTOS y CLIENTES.

1. Obtener, para cada departamento, cuantos DIRECTORES hay en es departamento y cual es su salario medio.

SELECT DEP_NO, count(OFICIO) AS 'Número directores', avg(SALARIO) AS 'Salario medio'

FROM EMPLEADOS

WHERE OFICIO LIKE "DIRECTOR"
GROUP BY dep_no;

2. Obtener los salarios medios por departamento, ordenados descendentemente por dicho importe, cuando dichos salarios medios sean inferiores a 3000 euros

SELECT DEP_NO AS Departamento, avg(SALARIO) AS 'Salario medio'

FROM empleados

GROUP BY dep_no

HAVING avg(SALARIO)<3000

ORDER BY 'Salario medio' desc;

3. Obtener el total de unidades por producto que hay entre todos los pedidos, visualizando el número de producto, la descripción y la suma.

SELECT PED.PRODUCTO_NO, DESCRIPCION, sum(UNIDADES) as 'Total unidades'

FROM pedidos ped, productos prod

WHERE ped.PRODUCTO_NO=prod.PRODUCTO_NO
GROUP BY prod.PRODUCTO_NO

4. Listar los números de cliente que tengan más de dos pedidos, ordenado por cantidad de pedidos

select CLIENTE_NO, count(PEDIDO_NO) 'N. Pedido'
from pedidos
group by CLIENTE_NO

having count(PEDIDO_NO)>2;
order by count(PEDIDO_NO);

5. Obtener las localidades en las que haya más de un cliente, visulizando cuanos clientes hay.

select LOCALIDAD, count(localidad AS 'N. Clientes'
from clientes

group by LOCALIDAD

having count(localidad)>1;

6. Obtener Los datos de los 4 productos de los que más unidades se han vendido, visualizando el número de producto y las unidades vendidas

select PRODUCTO_NO AS Producto, sum(UNIDADES) AS Cantidad

from pedidos

group by PRODUCTO_NO

order by Cantidad desc

limit 4;

Tablas utilizadas: EMPLEADOS , DEPARTAMENTOS, PEDIDOS, PRODUCTOS y CLIENTES.

1. Visualizar todos los oficios y el número de empleados de la empresa que los desempeñan

select OFICIO, count(OFICIO) as Empleados

from empleados

group by oficio;

2. Visualizar todos los oficios y el número de empleados de la empresa que lo desempeñan, siempre que sean más de uno

select OFICIO, count(OFICIO) as Empleados

from empleados

group by oficio

having count(OFICIO)>1;

3. Hallar la masa salarial (salario + comision) anual de la empresa, suponiendo 14 pagas

select sum((salario*14)+(ifnull(comision,0)*14)) AS 'Masa Salarial'

from empleados;

4. Obtener el número de empleados de la empresa que realizan cada oficio en cada departamento

select DEP_NO as Departamento, oficio, count(oficio) as Empleados

from empleados
group by dep_no, oficio;

5. Calcular el número de oficios distintos que hay en el departamento 30 de la tabla de empleados

select DEP_NO as Departamento, count(distinct oficio) as Oficios

from empleados

where DEP_NO=30;

6. Obtener las fechas de pedido del pedido más antiguo y el más reciente de cada producto, mostrando también el número de producto correspondiente

select producto_no AS Producto, min(FECHA_PEDIDO) AS 'Primer pedido', max(FECHA_PEDIDO) AS 'Ultimo pedido'

from pedidos

group by PRODUCTO_NO;

7. Obtener el total de euros que hay invertidos entre todos los productos disponibles

select sum(PRECIO_ACTUAL*STOCK_DISPONIBLE) AS 'Euros invertidos en productos'

from productos;

8. Obtener los salarios medios por departamentos de los vendedores y cuántos hay en cada departamento, siempre que ese salario medio sea > 1.200

select dep_no AS 'Departamento', avg(salario) AS 'Salario medio', count(oficio) AS 'Vendedores'

from empleados

where oficio like "VENDEDOR"

group by DEP_NO

having avg(salario)>1200;

9. Obtener el número de departamento con mayor salario medio de sus empleados, visualizando también el valor del salario medio correspondiente.

select DEP_NO AS Departamento, avg(salario) AS 'Salario medio'

from empleados

group by DEP_NO

order by avg(salario) desc

limit 1;

10. Visualizar las unidades totales vendidas de cada producto a cada cliente, mostrando el número de producto, el número de cliente y la suma de las unidades

select CLIENTE_NO AS Cliente, PRODUCTO_NO AS Producto, count(unidades) AS Unidades

from pedidos

group by CLIENTE_NO, producto_no;

11. Obtener aquellos productos de los que el número de pedidos que se han realizado, con la fecha del pedido anterior a 1 de enero del 2000, sea mayor de uno.

select PRODUCTO_NO AS Producto

from pedidos

where FECHA_PEDIDO<'2000-1-1'

group by producto_no

having count(PEDIDO_NO)>1

12. Obtener los números de cliente de los dos clientes con mayor número de pedidos, indicando también cuantos pedidos han hecho.

select CLIENTE_NO AS Cliente, count(PEDIDO_NO) AS Pedidos
from pedidos
group by CLIENTE_NO
order by count(PEDIDO_NO) desc

limit 2;