Resolución ejercicio final (26) - Listar los proveedores de los que ha comprado artículos un cliente determinado.

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

En esta entrada resolveremos el punto 23:
Listar los proveedores de los que ha comprado artículos un cliente determinado.

Se trata de generar un listado en el que se incluyan los proveedores de los productos que ha comprado un cliente.

Para resolverlo se utilizará una subconsulta, que proporcionará el listado de los ids de los artículos que ha comprado el cliente, mientras que otra consulta localizará los proveedores que nos han vendido esos artículos.

Como primer paso se creará una subconsulta que obtenga el nombre de los proveedores de un artículo determinado a partir de su id_articulo.

select distinct proveedores.id_proveedor, proveedores.nombre
from lin_ac inner join cab_ac
on lin_ac.id_ac = cab_ac.id_ac 
inner join proveedores
on cab_ac.id_proveedor = proveedores.id_proveedor
where id_articulo = 8;

Esta consulta devolverá los nombres de los proveedores del artículo de id 8.

Pero sería más útil si se le pudiese indicar varios ids simultáneamente.

Para ello se modifica la consulta cambiando el operador de comparación de la cláusula WHERE por el operador IN, que realiza una operación similar pero sobre un conjunto de datos.

La consulta queda finalmente como se indica a continuación:

select distinct proveedores.id_proveedor, proveedores.nombre
from lin_ac inner join cab_ac
on lin_ac.id_ac = cab_ac.id_ac 
inner join proveedores
on cab_ac.id_proveedor = proveedores.id_proveedor
WHERE id_articulo IN (8, 21, 350);

Esta consulta devolverá los nombres de los proveedores de los artículos de id 8, 21 y 350; en este caso se añade a la salida un nuevo proveedor.

Como segundo paso se creará otra consulta que obtendrá  los ids de todos los artículos comprados por un cliente.

Estos ids serán suministrados a la consulta anterior dentro de la cláusula IN.

select id_articulo
from lin_fv inner join cab_fv
on lin_fv.id_fv = cab_fv.id_fv
where cab_fv.id_cliente = 1;

La salida de esta consulta es un listado de ids de los artículos que ha comprado un cliente.

Se puede observar que los ids pueden salir repetidos.

Podría parecer útil añadir la subcláusula DISTINCT para que no existiesen repeticiones, pero se debe tener en cuenta que para eliminar los duplicados se le obliga a la Base de Datos a realizar una ordenación de los registros por el campo en que se desean eliminar los duplicados, y esta operación es una de las más lentas que se le pueden solicitar a una Base de Datos.

Por otra parte estos datos serán usados dentro de una cláusula IN, lo que hace innecesario la eliminación de duplicados.

La consulta final quedaría como se indica a continuación:


select distinct proveedores.id_proveedor, proveedores.nombre
from lin_ac inner join cab_ac
on lin_ac.id_ac = cab_ac.id_ac 
inner join proveedores
on cab_ac.id_proveedor = proveedores.id_proveedor
where id_articulo in (select id_articulo
from lin_fv inner join cab_fv
on lin_fv.id_fv = cab_fv.id_fv
where cab_fv.id_cliente = 2)
order by 1;

NOTA: Para facilitar la lectura del listado se ha añadido al final una cláusula ORDER BY 1, lo que indica que el resultado se ordene por el primer campo mostrado.

Resolución ejercicio final (23) - Descatalogar artículos.

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

En esta entrada resolveremos el punto 20:
Descatalogar artículos.

Se solicitará el código del artículo y se modificará el valor del campo Descatalogado.

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

El campo Descatalogado se ha creado como tipo BOOL, se constara que realmente se almacena como un valor de tipo TINYINT.

Los campos de tipo Booleano son aquellos que pueden representar valores verdadero (true) o falso (false), de acuerdo a las reglas del algebra de Boole.

NOTA: El algebra de Boole aparece por primera vez en el libro "An investigation of the Laws of Thought" publicado en el año 1854 por George Boole.

En un campo de tipo Bool, en una tabla de una Base de Datos SQL, se pueden almacenar los valores true y false.

Por lo tanto se trata simplemente de modificar el campo Descatalogado del valor false al valor true. 

NOTA: Por defecto en la consola de MySql se representará el false como un cero y el true como un uno.

update articulos set descatalogado = true
where id_articulo = 1;

Como se puede ver el valor que almacena el  campo descatalogado una vez puesto a true es 1.

El proceso contrario también se puede realizar, almacenando en el  campo descatalogado el valor false, que muestra en la consola un valor 0.

update articulos set descatalogado = false
where id_articulo = 1;

NOTA: Debido a que un campo tipo BOOL se almacena como un TINYINT, en realidad permite tratarlo como si fuese un dato numérico, almacenando directamente los valores cero y uno.

Para poner el valor falso (false) se asigna un cero. 

 update articulos set descatalogado = 0
 where id_articulo = 1;

Para poner el valor verdadero (true) se asigna un uno. 

 update articulos set descatalogado = 1
 where id_articulo = 1;

NOTA: Si se puede almacenar un cero o un uno, parece evidente que permitirá almacenar cualquier valor que se pueda almacenar en un TINYINT, en concreto un valor entre 0 y 127.

 update articulos set descatalogado = 111
 where id_articulo = 1;

Resolución ejercicio final (22) - Actualizar precios de Artículos.

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

En esta entrada resolveremos el punto 19:
Actualizar precios de Artículos.

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

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

Estado original:

update articulos set neto = neto * 1.10

where familia = "bebidas";

Estado final:

NOTA: Al modificar los precios en una base de datos se suele producir un aumento de decimales en los precios, por lo que suele ser común redondear los precios mediante la función ROUND..

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 (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 (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.

Resolución ejercicio final (14) - Listar Albaranes de un Proveedor

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

En esta entrada resolveremos el punto 11:
Listar Albaranes de un Proveedor.

Lo que se desea realmente es obtener los encabezados de las facturas de un único cliente.

select * from cab_fv where id_cliente = 1;

NOTA: Aunque la consulta anterior si que responde al punto 11 del enunciado, suele ser normal, añadir al lado del encabezado del albarán el total del mismo.

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 albarán.

Para ello se realizará un join a tres 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 cab_ac.*,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total
from cab_ac left join lin_ac
on cab_ac.id_ac = lin_ac.id_ac
inner join ivas
on lin_ac.iva = ivas.codigo_iva
where id_proveedor = 1
group by lin_ac.id_ac;

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