Resolución ejercicio final (21) - Listar artículos de un familia.

RESOLUCIÓN DEL PUNTO 18 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 18:
Listar artículos de un familia.

Se solicitará la familia y se mostrarán todos los artículos de la familia.

Se realizará una consulta a una única tabla (Articulos)

select * from articulos

where familia = "bebidas";

NOTA: Es una consulta muy sencilla en la que solo se filtra mediante un WHERE la condición deseada para la familia.

Resolución ejercicio final (20) - Localizar Artículos en almacenes.

RESOLUCIÓN DEL PUNTO 17 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 17:
Localizar Artículos en almacenes.

Se solicitará el código de un artículo, y la consulta deberá devolver los almacenes en los que el stock sea mayor de cero.

Se realizará un join a dos tablas (Ubicacion y Articulos)

select ubicacion.id_articulo,id_almacen, nombre, stock

from articulos inner join ubicacion
on articulos.id_articulo = ubicacion.id_articulo
where ubicacion.id_articulo = 8;

NOTA: En este caso solo es preciso realizar un INNER JOIN y filtrar los datos por código de artículo.

Al no haber filtrado el stock menor o igual que cero, aparecerán incluso aquellos almacenes en los que alguna vez hubo existencias. 

Resolución ejercicio final (19) - Ver stock por almacén.

RESOLUCIÓN DEL PUNTO 16 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 16:
Ver stock por almacén.

El proceso es similar al llevado a cabo para resolver el punto 15 Ver stock de todos los productos solo que en este caso es preciso ordenar por artículo y almacén.

Se realizará un join a dos tablas (Ubicacion y Articulos)

select ubicacion.id_articulo,id_almacen, nombre, stock
from articulos inner join ubicacion
on articulos.id_articulo = ubicacion.id_articulo
where stock > 0
order by id_almacen, ubicacion.id_articulo;

NOTA: No es preciso utilizar ninguna función de agrupado, por que solo existe una entrada para cada artículo y almacén.

Se usa un INNER JOIN para que solo salgan aquellos artículos de los que alguna vez ha habido stock, y estos a su vez son filtrados mediante un WHERE para mostrar solo aquellos que tengan un stock superior a cero unidades.

Resolución ejercicio final (18) - Ver stock de todos los productos.

RESOLUCIÓN DEL PUNTO 15 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 15:
Ver stock de todos los productos.

El proceso es idéntico al llevado a cabo para resolver el punto 14 Ver stock de un producto solo que en este caso no es preciso filtrar ningún código de artículo.

select articulos.id_articulo, nombre,
sum(lin_ac.cantidad) as compras,
sum(lin_fv.cantidad) as ventas,
(case when sum(lin_ac.cantidad) is null then 0 else sum(lin_ac.cantidad) end -
case when sum(lin_fv.cantidad) is null then 0 else sum(lin_fv.cantidad) end) as stock
from articulos left join lin_ac
on articulos.id_articulo = lin_ac.id_articulo
left join lin_fv
on articulos.id_articulo = lin_fv.id_articulo
group by id_articulo
having stock > 0;

NOTA: Se ha añadido la subcláusula HAVING para que solo salgan en el listado los artículos con stock, si se elimina esa subconsulta, se mostrarán todos los artículos, aunque no tengan stock.

Recordar que HAVING actúa como WHERE pero sobre los datos ya agrupados.

---

Otra posible solución es utilizar la tabla ubicación, en la cual actualizaremos el stock para cada compra y cada venta realizada.

select nombre, sum(stock) as total_stock
from articulos inner join ubicacion
on articulos.id_articulo = ubicacion.id_articulo
group by ubicacion.id_articulo
having total_stock > 0;

Resolución ejercicio final (17) - Ver stock de un producto.

RESOLUCIÓN DEL PUNTO 14 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 14:
Ver stock de un producto.

Para obtener el stock de un producto, según hemos organizado la Base de Datos, será preciso sumar todas las compras y restar todas las ventas.

Para ello se realizará un join a tres tablas.

select articulos.id_articulo, nombre,
 sum(lin_ac.cantidad) as compras, sum(lin_fv.cantidad) as ventas
 , (sum(lin_ac.cantidad) -  sum(lin_fv.cantidad)) as stock
 from articulos left join lin_ac
 on articulos.id_articulo = lin_ac.id_articulo
 left join lin_fv
 on articulos.id_articulo = lin_fv.id_articulo
 where articulos.id_articulo = 8
 group by id_articulo;

El resultado es:

En el caso de que el artículo no exista el resultado es un Empty Set:

Como ejemplo se muestra el resultado de la consulta de stock del artículo 12345, del cual no hay ni compras ni ventas. Lo cual se puede considerar correcto.

Sin embargo si el artículo existe, en la columna que no se encuentran datos, el resultado será NULL y, como es sabido las operaciones con valores NULL dan como resultado NULL.

Como ejemplo se muestra el resultado de la consulta de stock del artículo 323, del cual  hay compras pero no ventas.

NOTA: Si el artículo existe pero no tiene ni compras ni ventas los tres valores serán NULL.

Para evitar este problema, será preciso usar la expresión condicional CASE que forma parte del estándar SQL92.

Como solución se plantea la posibilidad de modificar el cálculo de la columna stock, mediante dos instrucciones CASE que transformen el valor NULL en un valor numérico cero.

select articulos.id_articulo, nombre,

sum(lin_ac.cantidad) as compras,

sum(lin_fv.cantidad) as ventas,

(case when sum(lin_ac.cantidad) is null then 0 else sum(lin_ac.cantidad) end -

case when sum(lin_fv.cantidad) is null then 0 else sum(lin_fv.cantidad) end) as stock

from articulos left join lin_ac

on articulos.id_articulo = lin_ac.id_articulo

left join lin_fv

on articulos.id_articulo = lin_fv.id_articulo

where articulos.id_articulo = 323

group by id_articulo;

En este caso a pesar de que el valor de la suma de las ventas es NULL, como se transforma en un cero mediante la sentencia CASE el cálculo del stock funciona correctamente.

---

Otra posible solución es utilizar la tabla ubicación, en la cual actualizaremos el stock para cada compra y cada venta realizada.

select nombre, sum(stock) as total_stock
from articulos inner join ubicacion
on articulos.id_articulo = ubicacion.id_articulo
where ubicacion.id_articulo = 8
group by ubicacion.id_articulo
having total_stock > 0;

Resolución ejercicio final (16) - Ver totales de cliente entre fechas.

RESOLUCIÓN DEL PUNTO 13 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 13:
Ver totales de cliente entre fechas.

Lo que se desea realmente es obtener la suma de todas las facturas de cada cliente, como en el punto anterior, pero añadiendo un filtrado entre fechas.

Para obtener el total en el listado es preciso realizar una consulta en la que además se sumen todas las líneas de cada factura agrupándola por cliente, tras haber sido filtradas por fechas.

Para ello se realizará un join a cuatro tablas.

Pero antes es preciso multiplicar el precio neto de cada artículo, por la cantidad de unidades, y por aplicarle el IVA correspondiente.

select clientes.id_cliente, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total
from clientes left join cab_fv
on clientes.id_cliente = cab_fv.id_cliente
left join lin_fv
on cab_fv.id_fv = lin_fv.id_fv
inner join ivas
on lin_fv.iva = ivas.codigo_iva
where cab_fv.fecha between "2012-11-22" and "2012-11-22"
group by clientes.id_cliente;

Para poder filtrar las facturas por fechas antes de proceder a la agrupación se ha añadido la penúltima línea:

En esta línea se añade una sentencia WHERE que filtra fechas mediante el operador BETWEEN, en este caso concreto para obtener las facturas de día 22 de Diciembre de 2012.

En caso de desear el listado de facturas entre dos fechas distintas, es suficiente con modificarlas.

where cab_fv.fecha between "2012-11-22" and "2012-11-22"

Como se había explicado, la sentencia WHERE filtra líneas, no grupos, por lo tanto aparecerán todos los grupos, pero estos se realizarán sólo sobre las líneas que permita WHERE. 

NOTA: Si al modificar la fechas del operador BETWEEN se indica la primera fecha superior a la segunda, se obtendrá como resultado un empty Set.

Obsérvese la diferencia al intentar obtener las facturas del mes de Noviembre.

En primer lugar filtraremos del primer día al último.

where cab_fv.fecha between "2012-11-01" and "2012-11-30"

El resultado es:

En segundo lugar filtraremos del último día al primero.

where cab_fv.fecha between "2012-11-30" and "2012-11-01"

El resultado es, en contra de lo esperado, un EMPTY SET:

Resolución ejercicio final (15) - Ver totales de cliente

RESOLUCIÓN DEL PUNTO 12 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 12:
Ver totales de cliente.

Lo que se desea realmente es obtener la suma de todas las facturas de cada cliente.

Para obtener el total en el listado es preciso realizar una consulta en la que además se sumen todas las líneas de cada factura agrupándola por cliente.

Para ello se realizará un join a cuatro tablas.

Pero antes es preciso multiplicar el precio neto de cada artículo, por la cantidad de unidades, y por aplicarle el IVA correspondiente.

select clientes.id_cliente, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total
from clientes left join cab_fv
on clientes.id_cliente = cab_fv.id_cliente
left join lin_fv
on cab_fv.id_fv = lin_fv.id_fv
inner join ivas
on lin_fv.iva = ivas.codigo_iva
group by clientes.id_cliente;

NOTA: Se han vinculado cuatro tablas mediante joins. Para que sea más fácil de visualizar el total se redondea con la función Round.