Solución ejercicios tema 7. Referencias cruzadas

Ejercicio 1

TRANSFORM SUM(importe)

SELECT rep AS empleado
FROM pedidos
WHERE year(fechapedido)=1990

GROUP BY rep

PIVOT MONTH(fechapedido);

Queremos calcular la suma de importes de los pedidos realizados durante el año 1990 agrupando por empleado y mes, queremos una fila por empleado luego GROUP BY rep, queremos que los meses aparezcan como columnas dinámicas, luego PIVOT MONTH(fechapedido), el calculo se hace en base a los pedidos de 1990 luego el origen de la FROM es pedidos con el WHERE para seleccionar los pedidos de 1990, como columna fija queremos el número de empleado luego la lista de selección será rep con un alias para que el resultado salga más aseado.

Ejercicio 2

TRANSFORM SUM(importe)

SELECT numemp AS empleado, nombre
FROM pedidos INNER JOIN empleados ON pedidos.rep=empleados.numemp
WHERE year(fechapedido)=1990

GROUP BY numemp, nombre

PIVOT MONTH(fechapedido);

Ahora queremos que además aparezca el nombre del empleado, como no está en la tabla pedidos sino en empleados, hay que añadir a los pedidos los datos del empleado con el INNER JOIN, ahora queremos dos columnas fijas, la del número de empleado y su nombre luego en la lista de selección añadimos nombre, y como estamos en una sumaria nombre no puede estar en la lista de selección si no está en el GROUP BY luego lo añadimos a la cláusula GROUP BY.

Ejercicio 3

TRANSFORM SUM(importe)

SELECT oficina
FROM pedidos RIGHT JOIN empleados ON pedidos.rep=empleados.numemp

GROUP BY oficina

PIVOT year(fechapedido)&"/"&MONTH(fechapedido);

Ahora queremos agrupar por oficina, año y mes, el pivote sería año y mes pero no se pueden poner dos campos en la cláusula PIVOT, lo que hacemos es unir los dos campos en uno mediantela concatenación & además para que queden los valores más claros los separamos por una barra.

Ejercicio 4

TRANSFORM COUNT(numemp)

SELECT oficina
FROM empleados

GROUP BY oficina

PIVOT year(contrato);

En este caso elegimos como pivote el año y como encabezado de fila la oficina ya que normalmente abrán más oficinas que años.

Ejercicio 5

TRANSFORM SUM(importe)

SELECT YEAR(fechapedido) AS anyo
FROM (pedidos INNER JOIN empleados ON pedidos.rep=empleados.numemp) INNER JOIN oficinas ON empleados.oficina=oficinas.oficina

GROUP BY YEAR(fechapedido)

PIVOT region;

En este ejercicios necesitamos los pedidos para el importe vendido y la tabla oficinas para la región y para unirlas debemos utilizar la tabla empleados para relacionar los pedidos con las oficinas de los empleados que han realizado el pedido.