Solución ejercicios tema 4. Las consultas de resumen

Ejercicio 1

SELECT AVG(cuota) AS cuota_media, AVG(ventas) AS ventas_media
FROM empleados

Sale una única fila con el resultado deseado. Siempre que se utilicen expresiones o funciones en la lista de selección, queda mejor utilizar un alias de columna para que ese aparezca en el encabezado del resultado.

Ejercicio 2

SELECT AVG(importe) AS importe_medio, SUM(importe) AS importe_total, AVG(importe/cant) AS precio_venta_medio
FROM pedidos

El precio medio de venta es la media aritmética de los precios unitarios de cada pedido. El precio unitario se calcula dividiendo el importe del pedido por la cantidad del pedido: importe/cant, por lo que ponemos AVG(importe/cant).

Ejercicio 3

SELECT AVG(precio) AS p_medio_ACI
FROM productos
WHERE idfab = 'ACI'

Ahora no nos interesan todos los productos sino unicamente los del fabricante ACI, por lo que añadimos la cláusula WHERE para que antes de calcular la media, elimine del origen de datos los registros que no cumplan la condición.

Ejercicio 4

SELECT SUM(importe) AS total_pedidos_V_Pantalla
FROM empleados INNER JOIN pedidos ON empleados.numemp = pedidos.rep
WHERE nombre = 'Vicente Pantalla'

El importe total lo tenemos que sacar de la tabla de pedidos, y además sólo nos interesan los de Vicente Pantalla. Como nos dan el nombre del representante en vez de su número y en el pedido sólo tenemos el número de representante tenemos que añadir a las líneas de cada pedido, los datos del representante correspondiente, por lo que el origen de datos debe ser el que aparece en la FROM.

Ejercicio 5

SELECT MIN(fechapedido) AS primer_pedido
FROM pedidos

La fecha del primer pedido es la fecha más antigua de la tabla de pedidos.

Ejercicio 6

SELECT COUNT(*) AS cuantos_pedidos_mayores
FROM pedidos
WHERE importe > 25000

Se podía haber utilizado también COUNT(numpedido) o cualquier nombre de columna que no pueda contener valores nulos, pero COUNT(*) es mejor por ser más rápido (la diferencia se nota con tablas muy voluminosas).

Ejercicio 7

SELECT oficina, COUNT(*) AS cuantos_empleados
FROM empleados
GROUP BY oficina

Con esta solución obtenemos el listado pedido pero no aparecen las oficinas que no tienen empleados asignados ya que sacamos la información de la tabla empleados y aparece una fila con valor nulo en oficina que contiene el número de empleados que no tienen oficina. Si quisieramos listar incluso las que no tengan empleados habría que recurrir a la solución 2

Solución 2
SELECT oficinas.oficina, COUNT(numemp) AS cuantos_empleados
FROM empleados RIGHT JOIN oficinas ON empleados.oficina = oficinas.oficina
GROUP BY oficinas.oficina

Utilizamos un RIGHT JOIN para que el origen de datos incluya también una fila por cada oficina que no tenga empleados.
En el GROUP BY y en la lista de selección hay que indicar el campo oficina de la tabla oficinas, si ponemos el de la tabla empleados, agrupará todas las oficinas que no tienen empleados en una fila (la columna empleados.oficina contiene valor nulo para esas filas).
Aquí no podemos utilizar COUNT(*) por que las oficinas sin empleados aparecerían con 1 en la columna cuantos_empleados ya que para esa oficina hay una fila.

Ejercicio 8

SELECT numemp, nombre, clie AS cliente, SUM(importe) AS total_vendido
FROM empleados INNER JOIN pedidos ON pedidos.rep = empleados.numemp
GROUP BY numemp, nombre, clie

Necesitamos la tabla de pedidos para el importe vendido a qué cliente, necesitamos la tabla empleados para el nombre del representante, la de clientes no la necesitamos ya que nos piden el número de cliente y este está en pedidos.
La agrupación básica que debemos realizar es por numemp y después por clie, pero como aparece el nombre del empleado en la lista de selección, hay que incluirlo también en el GROUP BY.
Después de determinar la agrupación básica que nos hace falta, siempre que se incluye una columna adicional en el GROUP BY hay que comprobar que esa nueva columna no cambia la agrupación básica.
Por ejemplo no podríamos añadir al GROUP BY la columna fechapedido ya que se formarían más grupos.

Solución 2
SELECT numemp, nombre, clie AS cliente, SUM(importe) AS total_vendido
FROM empleados LEFT JOIN pedidos ON pedidos.rep = empleados.numemp
GROUP BY numemp, nombre, clie

Si queremos que salgan todos los empleados incluso los que no aparezcan en los pedidos habría que sustituir el INNER por un LEFT.

Ejercicio 9

SELECT rep, AVG(importe) AS importe_medio
FROM pedidos
GROUP BY rep
HAVING SUM(importe) > 30000

No queremos todos los empleados, unicamente los que tengan un importe total pedido superior a 30.000, luego tenemos que poner la condición SUM(importe) > 30000. Como esta condición contiene una función de columna (SUM()) se tiene que poner en la cláusula HAVING ya que selecciona filas de la tabla resultante no del origen de datos.

Ejercicio 10

SELECT descripcion, precio, SUM(importe) AS total_pedido
FROM productos INNER JOIN pedidos ON pedidos.fab = productos.idfab AND pedidos.producto = productos.idproducto
GROUP BY idfab, idproducto, descripcion, precio, existencias
HAVING SUM(importe) > existencias * 0.75
ORDER BY 3

La agrupación básica es por idfab e idproducto ya que son los dos campos que conjuntamente identifican un producto.
Como descripción y precio aparecen en la lista de selección y no modifican la agrupación básica los incluimos en el GROUP BY.

Como existencias aparece en el HAVING y no modifica la agrupación básica lo incluimos también el el GROUP BY.

Para calcular el 75% de las existencias multiplicamos existencias por 0,75; observar que en la sentencia SQL hay que utilizar el punto para indicar los decimales.

Para indicar la columna de ordenación no podemos utilizar el alias campo, utilizamos el número de orden de la columna dentro de la lista de selección. En este caso la suma de importes es la tercera columna.

Ejercicio 11

Consulta: distintas_oficinas
SELECT DISTINCT oficina
FROM empleados
WHERE ventas > cuota
Consulta: sumaria11
SELECT COUNT(*) AS cuantas_oficinas
FROM distintas_oficinas

Si contamos las oficinas directamente de la tabla empleados nos salen 9 oficinas ya que la función COUNT(nb columna) cuenta los valores no nulos pero los valores repetidos los cuenta tantas veces como se repiten, como tenemos oficinas de se repiten en la columna oficina de la tabla oficinas, esas oficinas son contadas varias veces, hay que contar los valores distintos.
En otros SQL la función COUNT puede llevar delante del nombre de la columna la cláusula DISTINCT que indica que sólo se tienen que tener en cuenta valores distintos (no cuenta los repetidos), por ejemplo COUNT(DISTINCT oficina), es una opción muy útil que desgraciadamente no incluye el SQL de Microsoft JET. Para solucionar el problema se resuelve con dos consultas, una con la cual nos quedamos con los valores distintos (en la solución la consulta se llama distintas_oficinas), y la otra que nos cuenta esos valores.