6. Consultas Avanzadas con SQL#

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

Esfuerzo Necesario

El curso está organizado en 8 sesiones de clase. Cada clase (sesión) implica una dedicación de entre 2 y 4 horas.

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

Video Clase

6.1. Consultas SQL con una sola tabla#

Video Clase

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

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. Construyendo una consulta en SQL#

Video Clase

  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

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.3. Combinando consultas#

Video Clase

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

    1. UNION

    2. INTERSECT

    3. EXCEPT

    https://www.c-sharpcorner.com/article/the-complete-reference-set-operations-in-ms-sql-union-all-intersect-excep/Images/e6.png

  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

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.4. Consultando el valor NULL#

Video Clase

  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

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.5. Combinación de Tablas (JOIN)#

Video Clase

  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

      https://www.w3schools.com/sql/img_innerjoin.gif

    2. LEFT OUTER JOIN -> sql_join_left

      https://www.w3schools.com/sql/img_leftjoin.gif

    3. RIGHT OUTER JOIN -> sql_join_right

      https://www.w3schools.com/sql/img_rightjoin.gif

    4. FULL OUTER JOIN -> sql_join_full

      https://www.w3schools.com/sql/img_fulljoin.gif

  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)

Haz los ejercicios de consulta multitabla en Ejercicios para usar SQL

6.6. Seleccionando los campos a mostrar#

Video Clase

  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

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

6.7. Usando Subconsultas#

Video Clase

  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

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

6.8. Tablas de constantes y CTE con WITH#

Video Clase

  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

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