Consultas CTE en SQLite

Introducción

En este laboratorio, explorarás el poder de las Expresiones de Tabla Comunes (CTEs, Common Table Expressions) en SQLite. Aprenderás cómo definir y usar CTEs para mejorar la legibilidad y el mantenimiento de las consultas. Comenzarás con CTEs simples y luego pasarás a CTEs recursivas. Al final de este laboratorio, podrás usar CTEs para escribir código SQL más limpio, eficiente y fácil de entender.

Skills Graph

Crear una Base de Datos y una Tabla

En este paso, crearás una base de datos SQLite y una tabla employees (empleados). Esta tabla almacenará información de los empleados, que usarás en pasos posteriores para practicar consultas CTE.

Primero, abre tu terminal en la VM de LabEx. Tu ruta predeterminada es /home/labex/project.

Ahora, vamos a crear una base de datos SQLite llamada company.db. Ejecuta el siguiente comando para crear el archivo de la base de datos y abrir la herramienta de línea de comandos de SQLite:

sqlite3 company.db

Verás un indicador que muestra que ahora estás dentro del shell de SQLite:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

A continuación, crea una tabla llamada employees para almacenar información básica de los empleados. Esta tabla tendrá cuatro columnas: id, name (nombre), department (departamento) y salary (salario). Ingresa el siguiente comando SQL en el prompt sqlite> y presiona Enter:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

Este comando configura la tabla employees donde:

• id es un entero que sirve como clave primaria (primary key) para cada empleado.
• name es un campo de texto para el nombre del empleado.
• department es un campo de texto para el departamento del empleado.
• salary es un campo entero para el salario del empleado.

No verás ninguna salida si el comando se ejecuta correctamente.

Insertar Datos en la Tabla

Ahora que has creado la tabla employees (empleados), vamos a agregar algunos datos. Insertaremos cinco registros de empleados en la tabla.

Inserta cinco registros de empleados en la tabla employees ejecutando estos comandos uno por uno en el prompt sqlite>:

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Sales', 50000);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 60000);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Engineering', 70000);
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000);

Estos comandos agregan cinco filas a la tabla employees.

• INSERT INTO employees (name, department, salary) especifica que estás insertando datos en las columnas name (nombre), department (departamento) y salary (salario) de la tabla employees.
• VALUES ('Alice', 'Sales', 50000) proporciona los valores que se insertarán para cada registro.

Para confirmar que los datos se agregaron correctamente, ejecuta este comando para ver todos los registros en la tabla:

SELECT * FROM employees;

Resultado Esperado:

1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000

Esta salida muestra el id, name (nombre), department (departamento) y salary (salario) para cada registro. El comando SELECT * recupera todas las columnas de la tabla especificada.

Definir un CTE Simple

En este paso, aprenderás cómo definir y usar una Expresión de Tabla Común (CTE, Common Table Expression) simple en SQLite. Los CTEs son conjuntos de resultados temporales con nombre a los que puedes hacer referencia dentro de una sola declaración SQL. Son útiles para dividir consultas complejas en partes más pequeñas y manejables, mejorando la legibilidad y el mantenimiento.

Un CTE es esencialmente una subconsulta con nombre que existe solo durante la duración de una sola consulta. Defines un CTE usando la cláusula WITH, dándole un nombre y especificando la consulta que genera el conjunto de resultados. Luego puedes hacer referencia al nombre del CTE en la consulta principal como si fuera una tabla regular.

Sintaxis Básica:

WITH
    cte_name AS (
        SELECT column1, column2
        FROM table1
        WHERE condition
    )
SELECT column1, column2
FROM cte_name
WHERE another_condition;

Vamos a definir un CTE para seleccionar empleados del departamento de 'Sales' (Ventas). Ejecuta la siguiente declaración SQL en el prompt sqlite>:

WITH
    SalesEmployees AS (
        SELECT id, name, salary
        FROM employees
        WHERE department = 'Sales'
    )
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;

Esta consulta primero define un CTE llamado SalesEmployees que selecciona el id, name (nombre) y salary (salario) de todos los empleados en el departamento de 'Sales' (Ventas). Luego, la consulta principal selecciona el id, name (nombre) y salary (salario) del CTE SalesEmployees donde el salario es mayor que 52000.

Resultado Esperado:

3|Charlie|55000

Esto muestra que Charlie es el único empleado en el departamento de Ventas con un salario mayor a 52000.

Construir un CTE Recursivo

En este paso, aprenderás cómo construir y usar una Expresión de Tabla Común (CTE, Common Table Expression) recursiva en SQLite. Los CTEs recursivos se utilizan para consultar datos jerárquicos o con estructura de árbol. Te permiten recorrer las relaciones dentro de una tabla y recuperar datos en diferentes niveles de la jerarquía.

Un CTE recursivo es un CTE que se refiere a sí mismo. Consta de dos partes:

• Miembro Ancla (Anchor Member): La declaración SELECT inicial que define el caso base o el punto de partida de la recursión.
• Miembro Recursivo (Recursive Member): Una declaración SELECT que se refiere al propio CTE. Esta parte realiza el paso recursivo, basándose en los resultados de la iteración anterior.

El miembro ancla y el miembro recursivo se combinan utilizando el operador UNION ALL. La recursión continúa hasta que el miembro recursivo devuelve un conjunto de resultados vacío.

Primero, vamos a crear una tabla llamada employees_hierarchy (jerarquía_empleados) con la siguiente estructura:

CREATE TABLE employees_hierarchy (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER,
    title TEXT
);

Esta tabla representa la jerarquía de empleados, donde manager_id se refiere al id del gerente del empleado. Ejecuta el comando anterior en el prompt sqlite>.

A continuación, inserta algunos datos de ejemplo en la tabla employees_hierarchy:

INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Alice', NULL, 'CEO');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Bob', 1, 'VP of Marketing');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Charlie', 1, 'VP of Sales');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('David', 2, 'Marketing Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Eve', 3, 'Sales Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Frank', 4, 'Marketing Specialist');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Grace', 5, 'Sales Representative');

Aquí, Alice es la CEO (sin gerente), Bob y Charlie reportan a Alice, David reporta a Bob, Eve reporta a Charlie, Frank reporta a David y Grace reporta a Eve. Ejecuta los comandos anteriores en el prompt sqlite>.

Ahora, vamos a construir un CTE recursivo para recuperar toda la jerarquía bajo Alice (la CEO). Ejecuta la siguiente declaración SQL:

WITH RECURSIVE
    EmployeeHierarchy(id, name, manager_id, title, level) AS (
        -- Anchor member: Select the CEO
        SELECT id, name, manager_id, title, 0 AS level
        FROM employees_hierarchy
        WHERE manager_id IS NULL

        UNION ALL

        -- Recursive member: Select employees reporting to the current level
        SELECT e.id, e.name, e.manager_id, e.title, eh.level + 1
        FROM employees_hierarchy e
        JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    )
SELECT id, name, title, level
FROM EmployeeHierarchy;

Esta consulta define un CTE recursivo llamado EmployeeHierarchy (JerarquíaEmpleados). El miembro ancla selecciona a la CEO (donde manager_id es NULL). El miembro recursivo une la tabla employees_hierarchy con el CTE EmployeeHierarchy para encontrar empleados que reportan a los empleados seleccionados en el nivel anterior. La columna level (nivel) realiza un seguimiento de la profundidad en la jerarquía.

Resultado Esperado:

1|Alice|CEO|0
2|Bob|VP of Marketing|1
3|Charlie|VP of Sales|1
4|David|Marketing Manager|2
5|Eve|Sales Manager|2
6|Frank|Marketing Specialist|3
7|Grace|Sales Representative|3

Esto muestra toda la jerarquía de empleados, con el level (nivel) indicando el nivel de reporte.

Integrar CTEs en Consultas Complejas

En este paso, aprenderás cómo integrar CTEs en consultas más complejas en SQLite. Verás cómo usar múltiples CTEs dentro de una sola consulta.

Puedes definir múltiples CTEs en una sola consulta separándolos con comas. Esto te permite dividir una consulta compleja en varios pasos lógicos, cada uno representado por un CTE.

Vamos a crear una nueva tabla llamada department_salaries (salarios_departamento) con la siguiente estructura:

CREATE TABLE department_salaries (
    department TEXT,
    total_salary INTEGER
);

Esta tabla almacenará el salario total para cada departamento. Ejecuta el comando anterior en el prompt sqlite>.

Ahora, vamos a usar CTEs para calcular el salario total para cada departamento e insertar los resultados en la tabla department_salaries. Ejecuta la siguiente declaración SQL:

WITH
    DepartmentTotalSalaries AS (
        SELECT department, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department
    )
INSERT INTO department_salaries (department, total_salary)
SELECT department, total_salary
FROM DepartmentTotalSalaries;

SELECT * FROM department_salaries;

Esta consulta primero define un CTE llamado DepartmentTotalSalaries (SalariosTotalesDepartamento) que calcula el salario total para cada departamento utilizando la tabla employees (empleados). Luego, inserta los resultados del CTE DepartmentTotalSalaries en la tabla department_salaries. Finalmente, selecciona todos los datos de la tabla department_salaries para mostrar los resultados.

Resultado Esperado:

Sales|105000
Marketing|122000
Engineering|70000

Esto muestra el salario total para cada departamento.

Resumen

En este laboratorio, has aprendido cómo definir y usar Expresiones de Tabla Comunes (CTEs, Common Table Expressions) en SQLite. Comenzaste con CTEs simples para seleccionar datos de una tabla, luego pasaste a CTEs recursivos para consultar datos jerárquicos. Finalmente, aprendiste cómo integrar CTEs en consultas más complejas. Los CTEs son una herramienta poderosa para escribir código SQL más limpio, eficiente y fácil de entender.