INTRODUCCIÓN A SQL Y BASES DE DATOS RELACIONALES

INTRODUCCIÓN A SQL Y BASES DE DATOS RELACIONALES

SEMANA 5: CONSULTAS AVANZADAS Y OTROS TEMAS / CONSULTAS AVANZADAS MULTITABLA

CONSULTAS AVANZADAS MULTITABLA – VÍDEO 1

En este vídeo vamos a ver ejemplos de consultas más elaboradas en multitabla, ejemplos de consultas un poco más difíciles, el objetivo es que aprendáis a manejar todos los conceptos de SQL para hacer consultas de varias tablas con algo más de dificultad.

Para eso utilizaremos la base de datos de ejemplo

[pic 1]

de un distribuidor de alimentación, que tiene clientes, empleados, transportistas, productos y suministradores, y las categorías de los productos y las órdenes de los clientes con sus detalles de orden

Enlazadas por una serie de claves foráneas, o foreign key en inglés, en las órdenes, en los detalles de orden, y en los productos, pues en las órdenes tenemos el identificador de cliente, el de empleado y el de transportista, para saber quién ha encargado esa orden, qué empleado la vendió y quién la va a transportar.

En los detalles de orden tenemos una clave foránea del identificador de orden, para saber cada detalle de orden a qué orden va, y una de producto, para tener los detalles del producto, porque solamente tenemos un numerito, que nos identifica el producto, y en la de producto pues tenemos una clave foránea de suministrador, que nos da los datos del suministrador, y otra de categoría, para saber, el número de categoría y aquí tenemos el nombre y la descripción.

Bien, vamos a empezar con un primer SELECT que nos dé el total por cada orden,

[pic 2]

como habéis visto, las órdenes están en una tabla y los detalles de orden están en otra y los precios de los productos están en otro, con lo cual, por ahora, no necesitaremos la tabla de órdenes en esta todavía, ¿por qué?

Porque en los detalles de orden aparece ya un OrderID, con lo cual no vamos a necesitar enlazar la tabla de órdenes, siempre que no necesitemos algún campo adicional de la tabla de órdenes, que entonces sí que tendríamos que hacerlo como ahora veremos más adelante.

Bien, pues seleccionamos OrderID, bueno, aquí veréis que los SELECT ya están indentados, porque si no se hacen muy complicados de leer, el SELECT sencillo se puede poner todo seguido, de hecho esto lo podíamos haber puesto seguido pero sería peor. Con lo que vamos a hacer primero la subconsulta de dentro y luego haremos la de fuera.

Hacemos una subconsulta en la cual ponemos el detalle de orden, el identificador de orden, y luego multiplicamos la cantidad de la orden por el precio del producto, que viene en la tabla de productos, y a esta multiplicación le llamamos total (“AS Total”)

Aquí (FROM OrderDetails O) decimos que esto viene de la tabla de órdenes, que se identifica con el alias O, con un INNER JOIN, que podría ser LEFT JOIN también si quisiéramos, en función de cómo tengamos la integridad de la tabla. En principio, como esta tabla está dentro de la base de datos tiene integridad perfecta, da igual que sea INNER JOIN que sea LEFT JOIN, ¿vale?

¿con qué campo? P.ProductID=O.ProductID. A esta subconsulta le llamaremos Orderdetailswithprice, que son detalles de órdenes con precio en inglés.

Y luego esta subconsulta la pongo en el FROM de la consulta que va a hacer el resumen, que va a hacer OrderID, la suma del campo total que he definido aquí, como Ordertotal, o sea la cantidad total por cada orden, GROUP BY OrderID, agrupados por OrderID, ¿vale?

Entonces, pasamos aquí,

[pic 3]

que lo podéis hacer vosotros en este simulador, y fijaos que me da los 196 registros, que son las 196 órdenes que hay. Cada número de orden con su total, ¿de acuerdo? Perfecto, pues ya lo tenemos.

Siguiente, total por línea de orden con cliente, 

[pic 4]

fijaos que aquí sí que tenemos que enlazar los clientes que están en la orden con las cantidades por lo que tenemos que meter la tabla de órdenes en el JOIN.

Entonces ponemos SELECT, OD porque vamos a llamar O a las órdenes y OD a los detalles de órdenes, el campo OrderDetail, el campo OrderID, el campo CustomerName, porque no queremos hacer los resúmenes por id de cliente, sino que queremos por el nombre del cliente, y luego multiplicamos de OrderDetails la cantidad por el precio como total.

Lo traemos de detalles de órdenes OD, con un LEFT JOIN de productos P el ID de producto igual al ID de OrderDetails de producto, con un LEFT JOIN de órdenes, que se llama O, con el OrderID de OrderDetails igual al OrderID de órdenes, y con un LEFT JOIN de Customers, con el OrderID de Customers igual al OrderID de Orders, por eso tenemos las tres tablas.

Bien, esto nos va a devolver esto, ¿vale?

[pic 5]

Nos va a poner el nombre del cliente y el total de la cantidad en cada una de las líneas de orden.

Os propongo un ejercicio que es, sacar el total por cliente, a partir de esta consulta. Es combinar esta consulta, sustituirla en esta anterior y aquí donde pone OrderID, sería CustomerName, y aquí pues tendríais que cambiar GROUP BY CustomerName, y podríais hacer este mismo resumen, por cada OrderID pero aquí saldrían los nombres de los clientes con la cantidad total que han gastado, ¿vale?

Bien, eso es una propuesta que os hago.

Ahora vamos a hacer otra consulta, que son los cinco suministradores que más negocio generan.

[pic 6]

Pues aquí tenemos un SELECT, con un sub-SELECT dentro, que vamos a trabajar primero, ¿vale?

Aquí hacemos un SELECT de la tabla de suministradores el SupplierName, que es el suministrador, de la tabla suministradores, el país, luego de la tabla de OrderDetails la cantidad, y de la tabla de productos el precio,

¿por qué no metemos aquí las órdenes, trabajamos solo con el Details? Porque el SupplierName está enlazado en la tabla de productos, o sea el ID de Supplier, el ID de suministrador, está en la tabla de productos,

entonces cogemos FROM OrderDetails que llamamos O, con un LEFT JOIN con productos P, en el ProductID de producto, igual al ProductID de los OrderDetails, con un JOIN de los suministradores, que le llamamos S, con el SupplierID de producto, igual al SupplierID de suministrador. A todo esto le llamamos Orderdetailswithprice.

Y luego esto lo usamos como consulta, para alimentar un SELECT, en el cual ponemos el nombre del suministrador, su país y el redondeo de la suma de total, que habíamos definido aquí (en la subselección), como el total del suministrador.

Lo agrupamos por el nombre del suministrador, lo ordenamos por el total descendiente, de manera que nos salga primero el que más nos ha suministrado. En realidad, este no es el dinero que hemos pagado a los suministradores, nos está saliendo el negocio que nos ha generado, pues el que más negocio nos ha generado porque la cantidad con la que trabajamos es lo que le hemos vendido a los clientes, pues va a salir ordenado en forma descendente, y luego lo limitamos a cinco,

esto lo podéis hacer aquí, en este simulador y nos salen estos cinco,

[pic 7]

con su país, que vemos que hay dos franceses, y nos vemos el total de negocio que nos ha generado cada uno de estos cinco suministradores.

Bien, otro ejemplo: el número de productos por grupo de precios.

[pic 8]

En este caso, tenemos un SELECT con CASE, en el cual le asignamos un grupo de precios a cada uno de los productos, ¿vale?

Fijaos que es FROM Products, le decimos, hacer un CASE, que cuando el precio es menor de diez, decimos grupo uno, entre más de diez y menor o igual de cincuenta, dos, más de cincuenta y menor o igual de cien, tres, y si pasa de doscientos, cinco.  Esto lo tenemos aquí, y esto le llamamos GrupoPrecio FROM productos y hacemos un GROUP BY GrupoPrecio, ¿vale?

También lo he puesto aquí en as congrupos, por lo mismo, por el tema de que no me dé un error por no ponerle el nombre a su consulta, aunque no hace falta ponemos GrupoPrecio, contar GrupoPrecio como número de productos de esto.

Tenéis el simulador, que es el mismo, y fijaos que aquí

[pic 9]

me está diciendo que tenemos 14 productos, que valen diez o menos euros, 1 producto vale más de doscientos, y 1 producto vale entre cien y doscientos. y bueno, lo mismo, menos de cincuenta, y de entre cincuenta y cien. Pues, tenemos cada uno de los grupos de precios.

Y luego, una un poco más complicada, en el cual, vamos a necesitar enlazar las órdenes,

[pic 10]

porque queremos saber el total por categoría para mayo de 1997. Fijaros que tenemos el SELECT interior, cogemos el OrderDate, de la tabla de órdenes, el nombre de la categoría, de la tabla de categorías y la cantidad de OrderDetails, por el precio de productos, como total.

Y aquí ya tenemos todos los JOINS de OrderDetails con OD, con un LEFT JOIN, con productos P, en el ID de producto, con un LEFT JOIN con categorías C en la categoría ID, y con un LEFT JOIN, en órdenes, ¿por qué en órdenes?

Porque en orden está la fecha, que es lo que nos interesa ahora. ON OrderDetails igual a OrderID de OrderDetails igual a OrderID de órdenes. Y luego aquí dentro de este mismo SELECT, ya limitamos que nos devuelva sólo registros donde la fecha, el mes sea cinco y el año sea 1997, ¿vale? Y a esta subconsulta le llamamos OrderDetailwithPrice otra vez.

Y luego hacemos un GROUP BY, una consulta de agrupación sobre este SELECT, con el nombre la categoría y la función de redondeo de la suma del total de este campo, como total de categoría.

...