BBDD_2024_Unidad05_Pres2_99385df1234352eedc1d11529e255014.pdf

Base de Datos 1 Unidad 5 SQL Trabajo de Regularidad 2 Está disponible en la plataforma el segundo trabajo de regularidad. Consiste en el leer un artículo sobre el origen de SQL y responder unas preguntas Tiempo: Hasta el 1 de junio a las 8:00 Trabajo Práctico Combinando WHERE y ORDER B...

Base de Datos 1 Unidad 5 SQL Trabajo de Regularidad 2 Está disponible en la plataforma el segundo trabajo de regularidad. Consiste en el leer un artículo sobre el origen de SQL y responder unas preguntas Tiempo: Hasta el 1 de junio a las 8:00 Trabajo Práctico Combinando WHERE y ORDER BY Cuando los utilizamos juntos, la cláusula WHERE debe ir antes de la cláusula ORDER BY. EJEMPLO SELECT FirstName, LastName FROM Employees WHERE LastName >= 'N' ORDER BY LastName DESC; FirstName LastName ---------- -------------------- Michael Suyama Margaret Peacock Operadores de Condición La cláusula WHERE y las BETWEEN Devuelve valores en un rango inclusivo palabras operadores IN Devuelve valores que se encuentran en un conjunto específico LIKE Devuelve valores que coinciden con un patrón simple NOT Niega una operación  EJEMPLOS SELECT FirstName, LastName FROM Employees WHERE LastName BETWEEN 'J' AND 'M'; -- Lo anterior es equivalente a lo siguiente. SELECT FirstName, LastName FROM Employees WHERE LastName >= 'J' AND LastName = '19970212' AND OrderDate < '19970213';  Listar las órdenes del año 1997 SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE OrderDate >= '19970101' AND OrderDate < '19980101'; SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE YEAR(OrderDate)=1997; Filtrando rangos  Listar las órdenes de febrero del año 1997 SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders Filtrando WHERE OrderDate >= '19970201' AND OrderDate < '19970301'; rangos SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE YEAR(OrderDate)=1997 AND MONTH(OrderDate)=2; Presentación de los primeros n valores Agrupar y Resumir Agrupamiento en SQL Uso de las funciones de agregado Fundamentos del GROUP BY Generación de valores de agregado dentro de los conjuntos de resultados  COUNT() Devuelve la cantidad de filas que contienen valores NO nulos en un campo específico SUM() Devuelve la suma Funciones de AVG() Devuelve el promedio agregación y MAX() Devuelve el valor máximo MIN() Devuelve el valor mínimo agrupamiento Las funciones de agregación son utilizadas para calcular resultados utilizando campos de múltiples registros. Las cinco más comunes son:  CHECKSUM_AGG Devuelve un checksum del grupo. Puede ser utilizado para detectar cambios SQL Server cuenta además COUNT_BIG Como el COUNT, pero devuelve un con las siguientes funciones: valor bigint en lugar int STDEV/STDEVP Devuelve el desvío estándar estádistico o poblacional VAR/VARP Devuelve la varianza estadística o poblacional  BIT_AND() Devuelve AND bit a bit BIT_OR() Devuelve OR bit a bit BIT_XOR() Devuelve XOR bit a bit MySQL cuenta además GROUP_CONCAT() Devuelve una cadena concatenada con las siguientes JSON_ARRAYAGG() Devuelve resultado como un array JSON funciones: JSON_OBJECTAGG() Devuelve resultado como un objeto JSON STDDEV()/STDDEV_POP() Devuelve el desvío estándar estadístico o poblacional VARIANCE()/VAR_POP() Devuelve la varianza estadística o poblacional Uso de las funciones de agregado con valores nulos  DISTINCT puede utilizarse con funciones agregadas. SELECT COUNT(DISTINCT City) AS NumCiudades FROM Employees; NumCiudades ----------- 5 Seleccionando registros sin repetición  Total de unidades ordenadas Precio Unitario Promedio de del producto 3 los productos SELECT SUM(Quantity) AS SELECT AVG(UnitPrice) TotalUnidades AS PrecioPromedio FROM [Order Details] FROM Products; WHERE ProductID = 3; PrecioPromedio TotalUnidades --------------------- ------------- 28,8663 328 -- Encontrar la fecha de la primera y última contratación (HireDate) de empleados SELECT MIN(HireDate) AS PrimeraFechaAlta, MAX(HireDate) AS UltimaFechaAlta FROM Employees; PrimeraFechaAlta UltimaFechaAlta ----------------------- ----------------------- 1992-04-01 00:00:00.000 1994-11-15 00:00:00.000  Con GROUP BY las funciones de agrupación pueden ser aplicadas a grupos basados en los valores de sus campos. Por ejemplo, el número de empleados por ciudad: SELECT City, COUNT(EmployeeID) AS NumEmpleados FROM Employees GROUP BY City; City NumEmpleados --------------- ------------ Kirkland 1 London 4 Redmond 1 Seattle 2 Tacoma 1 Agrupando Datos Uso de la cláusula GROUP BY Having Mediante HAVING se pueden filtrar los resultados una vez que las funciones fueron calculadas. SELECT City, COUNT(EmployeeID) AS NumEmpleados FROM Employees GROUP BY City HAVING COUNT(EmployeeID) > 1; City NumEmpleados --------------- ------------ London 4 Seattle 2 Uso de la cláusula GROUP BY con la cláusula HAVING Orden de las cláusulas 1. SELECT 2. FROM 3. WHERE 4. GROUP BY 5. HAVING 6. ORDER BY  SELECT City, COUNT(EmployeeID) AS NumEmpleados FROM Employees WHERE Title = 'Sales Representative' GROUP BY City HAVING COUNT(EmployeeID) > 1 ORDER BY NumEmpleados; ciudad NumEmpleados --------------- ------------ London 3  Cada columna no calculada que aparece en el SELECT debe aparecer también en el GROUP BY. No se pueden utilizar alias en el HAVING Reglas de Se pueden utilizar alias en el ORDER BY Agrupamiento Sólo se pueden utilizar campos calculados en el HAVING Se deben utilizar alias de campos calculados o campos reales en el ORDER BY Subconsultas BBDD1 - UNPAZ ¿Que son las subconsultas? Las subconsultas son consultas embebidas dentro de otras consultas. Se utilizan para Obtener información de una tabla basada en información de otra tabla. Para dividir una consulta compleja en varios pasos Por lo general las tablas deben tener algún tipo de relación entre ellas. En dónde se pueden utilizar FROM Tablas derivadas SELECT Una expresión en SQL WHERE o HAVING Como una subconsulta correlacionada o autocontenida Uso de una Subconsulta como una tabla derivada Es un conjunto de registros dentro de una consulta que funciona como una tabla Ocupa el lugar de la tabla en la cláusula FROM Se optimiza con el resto de la consulta SELECT T.OrderID, T.CustomerID FROM ( SELECT OrderID, CustomerID FROM Orders ) AS T Uso de una Subconsulta como una expresión Se evalúa y trata como una expresión Se ejecuta una vez para la instrucción entera SELECT ProductName, UnitPrice, ( SELECT AVG(UnitPrice) FROM Products) AS Promedio, UnitPrice - ( SELECT AVG(UnitPrice) FROM Products) AS Diferencia FROM Products WHERE Discontinued = 1;  Obtener el clienteid de una orden específica es muy sencillo. SELECT CustomerID FROM Orders WHERE OrderID = 10290; CustomerID ---------- COMMI  COMMI es probable que no signifique mucho para quien pueda estar leyendo el resultado del reporte. Mediante la siguiente consulta, que utiliza subconsulta podemos ver un resultado más útil. SELECT CompanyName FROM Customers WHERE CustomerID = (SELECT CustomerID FROM Orders WHERE OrderID = 10290); Subconsultas La subconsulta puede contener cualquier SELECT válido, pero debe retornar en este caso una única columna con el número esperado de resultados. Si la subconsulta devuelve un solo valor se puede comparar por igualdad, desigualdad, mayor, menor, etc. Pero si la subconsulta devuelve más de un registro, la consulta deberá preguntar si el campo está dentro (IN) o no (NOT IN) del conjunto de valores devueltos.  -- Hallar los nombres de las compañías que efectuaron órdenes en 1997 SELECT CompanyName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE YEAR(OrderDate)=1997); Clasificación formal de las subconsultas De acuerdo a la cantidad esperada de valores que puede devolver una subconsulta, se las puede clasificar en: Escalar (Un solo valor) Multivaluada (Multiples Valores) Expresión de Tabla (Multiples valores en una Tabla Derivada) De acuerdo a la dependencia de la consulta principal, se las puede clasificar en: Autocontenida Correlacionada Subconsultas Autocontenidas Las subconsultas autocontenidas son independientes de la consulta principal a la que pertenecen, es decir, pueden ser ejecutadas de manera independiente. Por lo tanto, son simples para probar, ya que pueden probarse por separado Subconsultas Autocontenidas Escalares Dado que devuelve un único valor y es independiente de la consulta principal, puede aparecer en cualquier lugar de la consulta principal en que se necesite como en el SELECT o WHERE.  Por ejemplo, si necesitamos obtener los datos de la orden con el mayor id (ordenid) podemos ejecutar la siguiente consulta: SELECT OrderID, OrderDate, EmployeeID, CustomerID FROM Orders WHERE OrderID = (SELECT MAX(O.OrderID) FROM Orders AS O); OrderID OrderDate EmployeeID CustomerID ----------- ----------------------- ----------- ---------- 11077 1998-05-06 00:00:00.000 1 RATTC  Para que una subconsulta escalar sea válida debe retornar a lo sumo un valor. Si llega a devolver más de un valor se producirá un error en tiempo de ejecución. La siguiente consulta se ejecuta sin problema: SELECT OrderID FROM Orders WHERE EmployeeID = (SELECT E.EmployeeID FROM Employees AS E WHERE E.LastName LIKE 'C%');  Si una subconsulta escalar no devuelve ningún valor, devuelve NULL, por lo tanto, el predicado evalúa una comparación con NULL, que da como resultado Desconocido y por lo tanto la consulta principal no devuelve resultados. Por ejemplo, si buscamos empleado cuyo apellido empieza con A: SELECT OrderID FROM Orders WHERE EmployeeID = (SELECT E.EmployeeID FROM Employees AS E WHERE E.LastName LIKE 'A%'); Subconsultas Autocontenidas Multivaluadas Son consultas que devuelven múltiples valores para una misma columna. Los resultados de estas subconsultas deben evaluarse con predicados como IN. El formato de un predicado utilizando IN es: [NOT] IN () El predicado devuelve verdadero si la expresión escalar coincide con alguno de los valores devueltos por la subconsulta.  Si reescribimos uno de los ejemplos anteriores utilizando IN, ya no tendríamos problemas en tiempo de ejecución: SELECT OrderID FROM Orders WHERE EmployeeID IN (SELECT E.EmployeeID FROM Employees AS E WHERE E.LastName LIKE 'D%'); Subconsultas Correlacionadas Las subconsultas correlacionadas son subconsultas en las que se hace referencia a atributos que forman parte de la consulta principal. Esto significa que la subconsulta es dependiente de la consulta principal y no puede ser ejecutada de manera independiente. Lógicamente, es como si la subconsulta es evaluada por cada fila de la consulta principal.  Evaluación de una SubConsulta correlacionada 1) La consulta externa 2) La consulta interna pasa un valor de SELECT OrderID, CustomerID utiliza los valores que columna a la consulta FROM Orders AS O pasa la consulta externa interna WHERE 20 < ( SELECT Quantity FROM [Order Details] as OD WHERE O.OrderID = OD.OrderID AND OD.ProductID = 23) 3) La consulta interna devuelve un valor a la consulta externa 4) Este proceso se repite para fila siguiente de la consulta externa  Por ejemplo, la siguiente consulta que devuelve las órdenes con el máximo número para cada cliente (CustomerID): SELECT CustomerID, OrderID, OrderDate, EmployeeID FROM Orders AS O1 WHERE OrderID = (SELECT MAX(O2.OrderID) FROM Orders AS O2 WHERE O2.CustomerID = O1.CustomerID);  La consulta principal se realiza sobre una instancia de la tabla Ordenes que llamamos O1, que devuelve los valores para los que el campo ordenid coinciden con el resultado de la subconsulta. La subconsulta filtra los resultados de una segunda instancia de la tabla Ordenes que llamamos O2, en donde el clienteid de la tabla de la subconsulta es igual al clienteid de la tabla principal (O1). EXISTS También contamos en SQL con el predicado EXISTS, que devuelve verdadero (true) en caso de que la subconsulta devuelva alguna fila, de lo contrario devuelve falso (false). El formato de un predicado utilizando EXISTS es: [NOT] EXISTS ()  Por ejemplo, una consulta para obtener los clientes de España (Spain) que han realizado órdenes: SELECT CustomerID, CompanyName FROM Customers AS C WHERE Country = 'Spain' AND EXISTS (SELECT * FROM Orders AS O WHERE O.CustomerID = C.CustomerID);  Necesitamos los clientes de España que no realizaron órdenes: SELECT CustomerID, CompanyName Not Exists vs FROM Customers AS C Not In WHERE Country = 'Spain' AND NOT EXISTS (SELECT * FROM Orders AS O WHERE O.CustomerID = C.CustomerID);  SELECT CustomerID, CompanyName FROM Customers AS C Con NOT IN WHERE Country = 'Spain' AND CustomerID NOT IN (SELECT CustomerID FROM Orders AS O);  INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, ¿Entonces RequiredDate, ShippedDate, ShipVia, Freight, ShipName, da lo ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry) mismo? VALUES(NULL, 1, '20090212', '20090212', '20090212', 1, 123.00, 'abc', 'abc', 'abc', 'abc', 'abc', 'abc')  Si ejecutamos nuevamente las consultas, la de NOT EXISTS devuelve el mismo resultado La de NOT IN no, ¿por qué? Not Exists vs La diferencia se da por la lógica de tres estados. La expresión val IN (val1, val2, …, NULL) nunca Not In puede devolver FALSE. Sólo puede devolver TRUE o DESCONOCIDO Por lo tanto, la expresión val NOT IN (val1, val2, …, NULL) sólo puede devolver NOT TRUE o NOT DESCONOCIDO, ninguno de los cuales es TRUE  Para equiparar las respuestas debemos filtrar los valores NULL SELECT CustomerID, CompanyName Not Exists vs FROM Customers AS C Not In WHERE Country = 'Spain' AND CustomerID NOT IN (SELECT CustomerID FROM Orders AS O WHERE CustomerID IS NOT NULL); Combinación de varias Tablas JOINS Introducción Uso de alias en los nombres de las tablas Combinación de datos de varias tablas Combinación de varios conjuntos de resultados Uso de combinaciones cruzadas  Los joins permiten obtener información de múltiples tablas, para poder responder a preguntas como: ¿Qué productos son suministrados por qué proveedores? ¿Qué clientes realizaron qué órdenes? ¿Qué clientes están comprando qué productos? JOINS SINTAXIS SELECT tabla1.columna1, tabla2.columna2 FROM tabla1 JOIN tabla2 ON (tabla1.columna1=tabla2.columna1) WHERE condiciones Introducción a las combinaciones  Realizar un reporte para obtener el EmployeeID y el OrderID de la tabla Ordenes no es complicado: SELECT EmployeeID, OrderID FROM Orders; Pero para que pueda brindar información más útil, podemos hacer lo siguiente: -- Crear un reporte que muestre las ordenes de un empleado. SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.OrderDate FROM Employees JOIN Orders ON (Employees.EmployeeID = Orders.EmployeeID) ORDER BY Orders.OrderDate;