6. Consultas Avanzadas con SQL

6.1. ¿De qué va este tema?

Este tema es una continuación de lo ya visto en Consultando con SQL. Aprenderás, usando el SGBD Relacional PostgreSQL, a:

1. Conocer en detalle la sintaxis de SELECT en Postgres

2. Usar expresiones de tablas basadas en combinaciones de tablas

3. Conocer aspectos más avanzados de consultas SQL

6.2. Clases

Está organizado en 8 talleres de unas 3 sesiones de clase. En total unas 24 sesiones de clase más tu trabajo en casa.

La dedicación depende del conocimiento previo, motivación y capacidad de aprendizaje del estudiante para esa sesión en concreto.

Video Clase

6.2.1. Consultas SQL con una sola tabla

1. Repasa lo que ya hemos visto del lenguaje SQL (Consultando con SQL)

2. Las consultas se componen generalmente de:

   1. Lo que se selecciona (select_list). Campos, cálculos, constantes o funciones que devuelven valores

   2. El origen de la consulta (table_expression). Tablas básicas, relacionadas (JOIN), subconsultas, etc

   3. En qué orden lo muestra (sort_especification). Ordenamiento de los campos resultado de las consultas

3. La Sintaxis SQL SELECT en Postgresql (sql-select) incluye varios conceptos:

   1. expression (sql-expressions). Puede ser muchas cosas: constantes, cálculos, nombres de campos/columnas, funciones, etc

   2. condition. Es una expresión cuyo resultado es boolean (t/f)

   3. grouping_element. Campos para funciones de agrupamiento

   4. output_name / table_name / function_name / alias_name . Nombres de objetos o alias a objetos

   5. from_item. Origen de datos, sea una tabla o varias tablas. Puede no haber ningún origen de datos.

   6. join_type. Tipos de combinaciones de tablas

4. Las consultas pueden ser desde muy simples hasta más complejas (con más de una tabla en origen y lleva tiempo diseñarlas).

   -- Una muy simple
   
   select 1 as Ejemplo;
   
   -- Una más compleja:
   
   SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
   FROM products p LEFT JOIN sales s USING (product_id)
   WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
   GROUP BY product_id, p.name, p.price, p.cost
   HAVING sum(p.price * s.units) > 5000;
   

Ejercicios SQL

Video Clase

Para los ejercicios de repaso trabajas sobre Base de datos Hubway, en su versión Postgres.

Usa el listado de Ejercicios para usar SQL pero ahora usando la sintaxis SQL de Postgres.

6.2.2. Construyendo una consulta en SQL

1. Definimos una selección de columnas (value expressions) para seleccionar la información a mostrar (queries-select-lists)

   1. Define qué campos (o columnas) se van a seleccionar

   2. Incluye expresiones de valor, o sea que el resultado es un valor 22 o count(campo) o tabla1.campo2 o ...

   3. Es muy útil poner nombres a las columnas (para ayudar en la visualización u operar dentro de la consulta)

2. La selección es sobre un origen de datos «table expression», es decir una expresión que da como resultado una tabla ( queries-table-expressions). Por ejemplo:

   1. Una tabla completa p.ej. databasename.schemaname.tablaejemplo o parcial (sólo unos campos)

   2. Una expresión, que de como resultado una tabla p.ej. tabla1 cross join tabla2 where tabla1.id = tabla2.id and tabla3.edad > 50

   3. Un conjunto de valores (que da como resultado una tabla), p.ej values (1,'ejemplo'), (2,'otro')

   4. Es muy útil usar alias en las tablas (AliaS): permite usarlas en la consulta select t1.id from ejemplo AS t1;

3. Se calcula la tabla (temporal) sobre la que se seleccionarán las columnas:

   1. FROM table_reference [, table_reference [, …]]. Una (o más) tablas base o procesadas que generan la tabla (virtual) para consultar.

   2. WHERE search_condition. Condición lógica que deben cumplir los registros

   3. GROUP BY. Definición de la expresión a partir de la cual se agrupan (grouping_elements)

   4. HAVING. Condición sobre los datos agregados

4. Y finalmente se puede modificar cómo se mostrará la búsqueda:

   1. Ordenándola (ORDER BY)

   2. Limitándola (LIMIT) y Ajustándola (OFFSET)

Ejercicios SQL

Video Clase

Para los ejercicios de repaso trabajas sobre Base de datos Hubway, en su versión Postgres.

Usa el listado de Ejercicios para usar SQL pero ahora usando la sintaxis SQL de Postgres.

6.2.3. Combinando consultas

1. Se usan los operadores conjunto (queries-union)

   1. UNION

   2. INTERSECT

   3. EXCEPT

   

2. Las consultas tienen que ser «union compatible»:

   1. Igual número de columnas

   2. Tipos compatibles (no tienen porque ser 100% iguales) para los valores de las columnas

3. Uso del operador ALL:

   1. Modifica el comportamiento (devuelve los duplicados, si los hubiere)

   2. UNION ALL.

   3. INTERSECT ALL.

   4. EXCEPT ALL.

4. Usa paréntesis para organizar las consultas y evitar malentendidos (o errores de sintaxis)

   SELECT nombre FROM clientes UNION SELECT nom FROM vecinos LIMIT 10;
   (SELECT nombre FROM clientes UNION SELECT nom FROM vecinos) LIMIT 10; -- Es igual que la anterior
   SELECT nombre FROM clientes UNION (SELECT nom FROM vecinos LIMIT 10); -- Es diferente a las dos primeras
   

Uniendo Consultas

Video Clase

Crea una tabla nueva (tabla1) que sea el resultado de unir dos consultas de dos campos, en concreto:

1. Todos los campos

2. Sólo los comunes

3. Sólo los que no son comunes

Crea una tabla nueva que sea el resultado de unir dos consultas con 4 campos, en concreto:

1. Todos los campos

2. Sólo los comunes

3. Sólo los que no son comunes

6.2.4. Consultando el valor NULL

1. El valor NULL

   1. La columna no tiene ningún valor (es desconocido)

   2. Es diferente de que sea una cadena vacía (y no se distingue a simple vista)

2. Utilidad del valor null

   1. Insertar valores nulos en una tabla cuando no estemos claros (mejor que cadena vacia)

   2. Uso en las restricciones del modelo de datos:

      1. Restricción NOT NULL: el campo no puede tener valores nulos

      2. Restricción DEFAULT expression: valor predeterminado que nos interese si no se aporta

         create table ejemplo (
           n integer NOT NULL DEFAULT 0,
           s varchar(10) DEFAULT 'Vacío'
         );
         

   3. Nos permite detectar información en la que, probablemente haya que actuar.

3. Es necesario saber qué valores nulos hay en la tabla

   1. Uso de IS NULL y de IS NOT NULL

   2. Hay que decidir si se permiten o no (restricción NOT NULL)

   3. Influyen en las consultas, y hay que tener cuidado con ellos

Detectando valores NULL

Video Clase

Trabajas, en la versión Postgresql de la BD Base de datos Hubway.

1. ¿Hay valores nulos?

2. ¿En qué campos están?

3. ¿Cómo modificas el diseño físico para que no se acepten valores nulos?

4. ¿Puedes actualizar todos los (miles de) registros para que la BD quede en un estado «más fiable»?

5. ¿Qué decisiones vas a tomar para actualizar esos datos?

   1. ¿Qué hacemos con los valores nulos de start_station y end_station ?

   2. ¿Qué hacemos con los valores nulos de bike_number?

   3. ¿Y los de sub_type?

   4. ¿Y los de birth_date?

   5. ¿Y los de gender?

6.2.5. Combinación de Tablas (JOIN)

1. La cláusula JOIN (sql_join):

   1. Combina varias tablas que …

   2. Están relacionadas a través de una condición (o varias)

   3. Es muy útil cuando hay FK (pero no es obligatorio usar las FK )

   4. Pueden ser varias tablas relacionadas entre sí, no sólo dos

   5. Combina tablas añadiendo campos, mientras que UNION/EXCEPT/INTERSECT unen registros sobre los mismos campos (ojo a la diferencia)

2. Hay diferentes tipos de JOIN:

   1. (INNER) JOIN -> sql_join_inner

      

   2. LEFT OUTER JOIN -> sql_join_left

      

   3. RIGHT OUTER JOIN -> sql_join_right

      

   4. FULL OUTER JOIN -> sql_join_full

      

3. Un caso especial: SELF JOIN -> sql_join_self

4. Ojo a los campos nulos (NULL) que se generan al combinar tablas donde no puede asignar valores.

5. Algunos ejemplos de JOIN:

   SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
   FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
   
   -- Útil usar alias
   -- A veces es más claro con where y condición (ojo al      rendimiento)
   -- En este caso la combinación es CROSS JOIN (tablas separadas      por comas)
   
   SELECT O.OrderID, C.CustomerName, O.OrderDate
   FROM Orders O, Customer AS C
   WHERE O.CustomerID = C.CustomerID;
   

   -- Combinación de todos los campos de todas las tablas
   -- Los campos que  no se "cruzan" tienen valor NULL
   
   SELECT Customers.CustomerName, Orders.OrderID
   FROM Customers
   FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
   ORDER BY Customers.CustomerName;
   

Trabajando con combinación de tablas (JOIN)

Video Clase

Haz los ejercicios de consulta multitabla en Ejercicios para usar SQL

6.2.6. Seleccionando los campos a mostrar

1. Se usan «value expressions», es decir, cualquier tipo de cálculo, que como resultado, da un valor (sql-expressions).

2. Lo básico:

   1. Todos los campos (*)

   2. Seleccionar campos concretos de la tabla/s separados por comas

   3. Usando Etiquetas (Alias) de columnas: AS (opcional)

   SELECT * FROM Customers;
   SELECT ContactName, City FROM Customers;
   SELECT C.ContactName AS Contacto, City Ciudad FROM Customers C;
   

3. queries-select-lists

   1. Mismo nombre en diferentes tablas (tabla1.* vs tabla2.*)

   2. Constantes (números, cadenas, fechas)

   3. Funciones (que devuelven valores)

   4. Y … más (sql-expressions)

4. Uso de DISTINCT vs ALL

   1. ALL. Todos los valores seleccionados (comportamiento predeterminado)

   2. DISTINCT. Solo los valores distintos

      1. de toda la fila

      2. o sobre algunos campos vía DISTINCT on (expression)

   SELECT city FROM Customers;
   SELECT DISTINCT city FROM Customers;
   SELECT count(city) FROM Customers;
   SELECT count(distinct city) FROM Customers;
   

5. Concatenación de campos:

   1. Permite «crear informes sencillos»

   2. Usa el operador concatenación ||

   SELECT (CustomerName || ' tiene como contacto a ' || ContactName || ' y vive en ' || City) AS Informe
   FROM Customers;
   

Ejercicios SQL

Video Clase

Haz los ejercicios de Ejercicios de SQL sobre BD Northwind (W3Schools)

6.2.7. Usando Subconsultas

1. Se usan consultas dentro de consultas (subquery)

2. Como expresiones de valor: el resultado de la consulta es un valor

   SELECT codigo FROM Pedidos
   WHERE precio > (SELECT max(precio) FROM OtrosPedidos);
   

3. Como expresiones de tabla: el resultado de la consulta es una tabla

4. Y también como uso de expresiones de consulta (subquery expressions : functions-subquery)

   1. Devuelven true/false como resultado de una operación con una subconsulta.

   2. EXISTS (subquery): Devuelve true/false si existe al menos un resultado (o no)

      SELECT col1
      FROM tab1
      WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
      
      -- Se usa select 1, para no malgastar tiempo de proceso seleccionando campos
      -- sólo necesita saber que existe algún resultado
      

   3. expression IN (subquery): como argumento al operador IN (o NOT IN).

      La subconsulta no devuelve un valor, sino una tabla de una columna (table expression)

      SELECT nombre
      FROM editoriales
      WHERE codigo IN
              (select codigoeditorial
              from libros
              where autor='Richard Bach');
      

      Es equivalente a SOME/ANY: expression operator ANY (subquery)

      SELECT nombre
      FROM editoriales
      WHERE codigo ANY
              (select codigoeditorial
              from libros
              where autor='Richard Bach');
      

5. Y otro caso especial es expression operator ALL (subquery)

   1. Si la expression se cumple para todos los valores de la columna resultado de la subconsulta

   2. NOT IN es equivalente a <> ALL

Ejercicios SQL

Video Clase

Haz los ejercicios de Ejercicios de SQL sobre BD Northwind (W3Schools)

6.2.8. Tablas de constantes y CTE con WITH

1. Uso de VALUES (queries-values): tabla estática y constante:

   1. Campos separados por comas

   2. Organizados en registros usando paréntesis

   3. Registros separados con comas

   4. El tipo de datos de cada campo se adecúa al valor

   5. Se usa como una tabla normal (table_expression)

   6. Útil añadirle una etiqueta de tabla (y campos)

2. Ejemplos de uso:

   SELECT ejemplo.numero
   FROM
   (
          VALUES (1, 'uno'), (2, 'dos'), (3, 'tres')
   ) AS ejemplo (numero,texto);
   
   INSERT INTO films (code, title, did, date_prod, kind)
      VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
   
   UPDATE employees SET salary = salary * v.increase
   FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)
   WHERE employees.depno = v.depno AND employees.sales >= v.target;
   

3. Uso de WITH (queries-with):

   1. También se denominan CTE (Common Table Expressions)

      WITH regional_sales AS (
      SELECT region, SUM(amount) AS total_sales
      FROM orders
      GROUP BY region
      ), top_regions AS (
      SELECT region
      FROM regional_sales
      WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
      )
      SELECT region,
      product,
      SUM(quantity) AS product_units,
      SUM(amount) AS product_sales
      FROM orders
      WHERE region IN (SELECT region FROM top_regions)
      GROUP BY region, product;
      

   2. Son temporales, y sólo se usan en el contexto de esa consulta

   3. Su principal objetivo es simplificar la consulta (más legible y mejor estructurada) cuando es muy compleja

   4. Se usa no solo con SELECT (también sirven con INSERT, DELETE, UPDATE)

   5. Construcción (WITH Clause ):

      1. Defines las consultas temporales (p.ej cte_sales y shipper_cte)

      2. Vinculadas a una consulta (o modificación de datos)

      3. Usas los nombres CTE en la consulta principal

      -- Primera CTE: se prepara la tabla cte_sales
      
      WITH cte_sales
      AS
      (SELECT
      EmployeeID,
      COUNT(OrderID) as Orders,
      ShipperID
      FROM Orders
      GROUP BY EmployeeID, ShipperID),
      
      -- Segunda CTE: shipper_cte (que usa la primera, cte_sales)
      
      shipper_cte
      AS
      (SELECT *
      FROM cte_sales
      WHERE ShipperID=2 or ShipperID=3)
      
      -- Consulta usando la segunda CTE (que a su vez usa la primera)
      
      SELECT
      ShipperID, AVG(Orders) average_order_per_employee
      FROM
      shipper_cte
      GROUP BY ShipperID;
      

Organizando y entendiendo consultas complejas

Video Clase

Tomando como referencia este excelente artículo (ojo, que es un poco complejo), la idea es entender:

1. Los diferentes elementos de la Consulta Original : consulta compleja con muchos elementos

2. El uso de WITH y su uso en la Consulta Refactorizada

3. Y una última consulta mejorada y ampliando la idea original

---