Clase 15 - Guion de clase(1)

Vista previa del material en texto

Clase 15 - Lenguaje Estructurado de Consulta SQL. Parte 2
Introducción
En la clase anterior pudimos ver los comandos de consultas básicas y de selección (SELECT) simple con SQL. En esta clase veremos otros tipos de comandos, y consultas más complejas. 
Inserción de filas en una tabla
La inserción de nuevos registros se realiza con la sentencia INSERT INTO especificando la tabla en la que se insertará y las columnas para las cuales se insertarán valores. Por ejemplo, para agregar un nuevo producto a la tabla Products
INSERT INTO Products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price)
VALUES (78, 'Queso Crema', 2, 4, '1 kg pkg.', 30);
Es preciso notar que siempre debe existir correspondencia entre el orden de las columnas que se y los respectivos valores que se proveen. Por ejemplo, el ejecutar 
INSERT INTO Products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price)
VALUES ('Queso Crema', 78, 2, 4, '1 kg pkg.', 30);
resultará en un error ya que se está intentando registrar un dato de tipo CHAR en la clave primaria de la tabla.
Modificación de filas de una tabla
La modificación de registros se realiza con la sentencia UPDATE, explicitando los nuevos valores para los campos que se quieren actualizar. Por ejemplo, para actualizar el precio del producto número 5 se puede ejecutar
UPDATE Products 
SET Price = 100
WHERE ProductID = 5
Puede verificar que efectivamente se haya actualizado el precio ejecutando
SELECT * FROM Products
WHERE ProductID = 5;
_________________________________________________________________________
Importante: Si no se provee ninguna cláusula WHERE, la modificación se realizará para todos las filas de la tabla sobreescribiendo los valores previos. Por lo tanto, procure siempre incluir la correspondiente cláusula WHERE cuando modifique registros.
_________________________________________________________________________
UPDATE Products 
SET Price = 100;
SELECT * FROM Products;
Borrado de filas de una tabla
El borrado de registros se realiza con la sentencia DELETE FROM acompañada de la correspondiente sentencia WHERE.
DELETE FROM Products
WHERE ProductID >= 5;
SELECT * FROM Products;
_________________________________________________________________________
Importante: Al igual que con la sentencia UPDATE, se eliminarán todas las filas de la tabla si no se provee ninguna cláusula WHERE.
_________________________________________________________________________
Consultas de mayor complejidad
En ocasiones se requiere involucrar múltiples tablas en una misma consulta. Por ejemplo, si se quisiera mostrar todos los productos con su correspondiente nombre y descripción de categoría habría que reunir la información contenida en la tabla Products con la contenida en la tabla Categories. Esta vinculación puede realizarse con la sentencia JOIN, que permite reunir tablas por medio de la clave foránea.
SELECT p.ProductName, p.CategoryId, c.CategoryName, c.Description
FROM Products AS p
JOIN Categories AS c
	ON p.CategoryID = c.CategoryID;
La condición de reunión se especifica en la cláusula ON, en donde se expresa que se requiere reunir las tablas haciendo coincidir la clave foránea de categoría de la tabla Products con la clave primaria de la tabla Category. Esta cláusula no debe olvidarse, ya que en caso de no especificar ninguna condición de reunión se obtendrán resultados inválidos. Pruebe ejecutar
SELECT p.ProductName, p.CategoryId, c.CategoryName, c.Description
FROM Products AS p
JOIN Categories AS c;
y observe que la primera y segunda fila tienen el mismo CategoryID pero sin embargo el CategoryName es diferente. 
Al no especificar condición de reunión, la base de datos está emparejando cada fila de la primera tabla con cada fila de la segunda, de manera que no refleja fielmente la relación de la información almacenada.
Ordenar el resultado de una consulta
Es posible ordenar el resultado de una consulta según una determinada columna por medio de la cláusula ORDER BY, como se muestra a continuación
SELECT * FROM Products
ORDER BY Price;
Por defecto, se ordenarán las filas de manera ascendente, es decir, los productos con menor precio aparecerán primero en la lista. Puede especificar lo contrario con
SELECT * FROM Products
ORDER BY Price DESC;
que indica un ordenamiento descendente, de manera que los productos más caros aparecerán primeros.
Agregaciones
Las agregaciones permiten calcular medidas resúmen acerca de los datos contenidos en una determinada columna. Por ejemplo, para calcular el precio máximo, mínimo y promedio de los productos registrados puede ejecutar
SELECT MIN(Price) AS PrecioMínimo, 
	MAX(Price) AS PrecioMáximo, 
	AVG(Price) AS PromedioDePrecios 
FROM Products;
Por otro lado, con la agregación COUNT es posible contar la cantidad de productos registrados de una determinada categoría
SELECT COUNT(ProductID) AS CantidadDeProductosCategoría8
FROM Products
WHERE CategoryID = 8;
o bien calcular el total de precios de los productos con categoría 8 con la agregación SUM
SELECT SUM(Price) AS TotalPreciosProductosCategoría8
FROM Products
WHERE CategoryID = 8;
Agrupaciones
Las agrupaciones permiten formar grupos de acuerdo a una determinada columna, y calcular medidas resúmen sobre ellos. Por ejemplo, es posible obtener un listado que muestre el precio promedio de los productos según su categoría. Para ello, se deberán agrupar todos los productos con la misma categoría y calcular el promedio de precio en cada categoría por separado. Esto se realiza con la sentencia GROUP BY, seguida de las columnas incluidas en SELECT pero que no son agregaciones, tal como se muestra a continuación
SELECT CategoryID, AVG(Price) 
FROM Products
GROUP BY CategoryID;
También es posible aplicar filtros a cada uno de los grupos con la sentencia HAVING. Se debe tener en cuenta que este tipo de filtro es a nivel de grupo, es decir, es el grupo entero de registros el que se evalúa si cumple la condición o no y por tanto no deben con los filtros a nivel de fila que se realizan en la cláusula WHERE.
Por ejemplo, es posible obtener un listado de aquellas categorías de productos cuyo precio promedio es menor o igual a 25
SELECT CategoryID, AVG(Price) AS PromedioPrecio
FROM Products
GROUP BY CategoryID
HAVING PromedioPrecio <= 25; 
Para ordenar los promedios de precios de menor a mayor, se debe incluir la sentencia ORDER BY siempre al final de la consulta
SELECT CategoryID, AVG(Price) AS PromedioPrecio
FROM Products
GROUP BY CategoryID
HAVING PromedioPrecio <= 25
ORDER BY PromedioPrecio;
La cláusula HAVING también acepta múltiples condiciones de grupo, unidas mediante los operadores AND o OR según corresponda
SELECT CategoryID, AVG(Price) AS PromedioPrecio, MIN(Price) as PrecioMinimo
FROM Products
GROUP BY CategoryID
HAVING PromedioPrecio <= 25
	AND PrecioMinimo > 6; 
En ocasiones se requiere formar grupos únicamente considerando algún subconjunto de filas de la tabla. Para ello, se debe incluir el filtro a nivel de fila con la cláusula WHERE justo antes de la cláusula GROUP BY. Por ejemplo, para generar un listado con el promedio de precios según categoría para los productos provistos por el proveedor 1:
SELECT CategoryID, AVG(Price) AS PromedioPrecio
FROM Products
WHERE SupplierID = 1
GROUP BY CategoryID
ORDER BY PromedioPrecio;
Lenguaje de Control de Transacciones (TCL)
Las sentencias INSERT, UPDATE y DELETE vistas hasta el momento realizaban modificaciones en la base de datos de manera directa. Una vez ejecutada alguna de ellas, no era posible revertir la operación para recuperar los valores anteriores, lo que significaba que los cambios se realizaban de manera definitiva. Esta forma de operar trae consigo algunos inconvenientes, ya que no resguarda a la base de datos de los posibles fallos que puedan ocurrir durante la ejecución de las operaciones, pudiendo provocar así inconsistencias en la información almacenada.
Para ilustrar , considere la base de datos de un banco. Suponga que Matias realiza una transferencia de $10.000 hacia Esteban.Pensándolo en términos de operaciones en la base de datos, esta transferencia debería reflejarse en la misma como una reducción de $10.000 en el monto de la cuenta corriente de Matías y un incremento de igual monto en la cuenta corriente de Esteban. Realizar esto implica ejecutar dos operaciones UPDATE en los registros de una misma tabla, para lo cual se deben realizar dos sentencias SQL distintas: una que disminuya el saldo de Matías y otra que aumente el saldo de Esteban. Ahora bien, ¿qué sucedería si, por algún fallo en el servidor, se ejecuta la primera sentencia UPDATE pero la segunda no?. Ciertamente, esto implica una pérdida en la integridad de los datos ya que tras el fallo, el flujo neto de saldos del banco es distinto de cero. Matías vería un débito en su cuenta pero Esteban no obtendría el correspondiente aumento de fondos.
Transacciones
En situaciones como la descrita, es de suma importancia que una serie de operaciones de DML se ejecuten conjuntamente como si se tratase de una única operación. Para ello es que se implementan las transacciones. En SQL, una transacción consta de un conjunto de operaciones que se ejecutan como si se tratase de una unidad lógica de trabajo.
Para indicar que el comienzo de una transacción, se debe ejecutar
START TRANSACTION;
A continuación, todas las sentencias SQL que se ejecuten quedarán grabadas temporalmente pero no se realizarán modificaciones físicas en la base de datos hasta que el usuario confirme la transacción. Durante una transacción, podemos pensar que estamos trabajando con una base de datos “virtual” la cual es una copia de la base de datos original. Para confirmar una transacción exitosa se ejecuta
COMMIT;
que indica que finalmente se pueden escribir de manera definitiva los cambios realizados.
Si en medio de transacción ocurre un fallo, el usuario puede abortar la transacción y volver al estado previo de la base de datos ejecutando
ROLLBACK;
La gran mayoría de los sistemas de bases de datos soportan el manejo de transacciones, sin embargo, lamentablemente nuestra base de datos de ejemplo de W3Schools tiene deshabilitada este tipo de operaciones. De todas formas, se desarrollará a continuación un ejemplo utilizando el modelo de datos de la misma.
Ejemplo: registrando una venta
Considere que se debe registrar una venta con su detalle, el cual consta de un conjunto de productos y su respectiva cantidad comprada. En este caso es preciso utilizar una transacción dado que ante un fallo en el registro del detalle quedará reflejada en la base de datos una orden vacía o incompleta.
La base de datos del ejemplo contiene las tablas Orders y OrderDetails, en donde esta última posee una clave foránea en la columna OrderID que apunta a la columna OrderID de la tabla Orders. Dado que OrderDetails referencia a una fila de Orders, deberá primero registrarse la orden y posteriormente su respectivo detalle.
En primer lugar se deberá iniciar la transacción con
START TRANSACTION;
enseguida se deberá registrar la orden
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, ShipperID)
VALUES (15, 5, '2021-01-01', 2);
Hasta este momento, la nueva orden se encontraría escrita de manera temporal en la tabla virtual. Si la herramienta lo permitiese, sería posible verificar esto realizando un SELECT sobre la tabla Orders
SELECT * FROM Orders 
WHERE OrderDate = '2021-01-01';
lo cual retornaría la fila recientemente insertada.
Si en este momento se aborta la transacción ejecutando
ROLLBACK;
se vería que el INSERT no se ha escrito de manera definitiva, dado al volver a consultar la tabla con la sentencia
SELECT * FROM Orders 
WHERE OrderDate > '2021-01-01';
no retornaría ningún resultado.
Ahora bien, para simular un registro completo de la venta, habría que ejecutar el siguiente conjunto de sentencias finalizando con la confirmación de la transacción
START TRANSACTION;
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, ShipperID)
VALUES (15, 5, '2021-01-01', 2);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
VALUES 
	(10444, 11, 5),
	(10444, 12, 10),
 	(10444, 2, 80);
COMMIT;
Tras ejecutar COMMIT, ambas inserciones quedarían asentadas de manera definitiva en la base de datos y a partir de aquí se podría verificar esto ejecutando
SELECT o.OrderID, o.OrderDate, od.OrderDetailID, od.ProductID, od.Quantity
FROM Orders AS o
JOIN OrderDetails AS od
	ON o.OrderID = od.OrderID
WHERE o.OrderDate = '2021-01-01';