Expresiones de Tablas Comunes (CTE) en SQL

No figura en recursos el script de la base de datos

```js WITH AverageSalaries AS ( SELECT d.department_name, AVG(e.salary) AS AvgSalary FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name ), HighSalaryDepartments AS ( SELECT department_name, AvgSalary FROM AverageSalaries WHERE AvgSalary > 45000 ) SELECT e.first_name, e.last_name, e.salary, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN HighSalaryDepartments hsd ON d.department_name = hsd.department_name WHERE e.salary > hsd.AvgSalary; ``` ### Ventajas de Usar CTE 1. **Legibilidad**: Hace que el código sea más fácil de leer y entender. 2. **Modularidad**: Permite dividir una consulta compleja en partes más manejables. 3. **Reutilización**: Se pueden definir una vez y usar múltiples veces dentro de la misma consulta. Las CTE son una herramienta poderosa para escribir consultas SQL más claras y eficientes.

Hace un tiempo le comente a Fredy en fb sobre la falta de las CTE en los curso de BD's ahora veo que si leen lo que les comentamos jajaja Les recomiendo no quedarse con lo de esta clase, las CTE puede ser el cambio entre dia y noche en el tiempo de procesamiento de un query de alto nivel, sobre todo si empezamos a limitar con una subquery todas las demás que tengamos dentro del CTE.

Esto de las CTE me recuerda a cuando en programación uno crea funciones más pequeñas sobre pasos específicos de un algoritmo para luego invocar a estas en una función "main" o "principal" que ejecuta todos los pasos, cosa que mejora mucho la legibilidad y la capacidad de debuggear el código. Pensarlo con esa analogía me sirvió mucho y más porque vengo de codear en lenguajes como Java o Python donde se usa mucho eso. Espero que a alguien más le funcione pensarlo así :)

Que mal fue evolucionando este curso, voy hasta el final porque no me gusta dejar las cosas a medias, pero voy con más dudas que respuestas...

Caro 👩‍💻 identifique que son los Departament: `WITH AverageSalaries AS (` ` SELECT d.DepartmentName AS Departamento, AVG(e.Salary) AS SueldoPromedio` ` FROM Employees e` ` JOIN Departments d ON e.DepartmentID = d.DepartmentID` `)`

La mala practica al renombrar las tablas es debido a que estamos utilizando una sola letra lo cual podría ser confuso porque no nos da información de donde pertenecería esa tabla.

¿Dónde están los parámetros para crear la base de datos? No hay los recursos para la base de datos.

Mi CTE: `WITH sumary_salaries AS (` ` SELECT d.departament_name, SUM(e.salary) AS sumary_salary` ` FROM EMPLOYEE e` ` JOIN DEPARTAMENT d ON e.departament_id = d.departament_id` ` GROUP BY d.departament_name` `),` `average_salaries AS (` ` SELECT d.departament_name, AVG(e.salary) AS AVG_salary` ` FROM EMPLOYEE e` ` JOIN DEPARTAMENT d ON e.departament_id = d.departament_id` ` GROUP BY d.departament_name` `)` `SELECT s.departament_name, s.sumary_salary` `FROM sumary_salaries s` `JOIN average_salaries a ON s.departament_name = a.departament_name` `WHERE s.sumary_salary > a.AVG_salary / 10;` Resultado: 

me gustó aprender sobre las CTE ya que tengo experiencia en SQL pero este concepto para alguien que no sabe nada de sql se le hara dificil de entender

## ¿Qué son las CTE en SQL? **CTE** son las siglas de **Common Table Expression** (Expresión de Tabla Común) en español. Son como tablas temporales que se definen dentro de una consulta SQL y solo existen durante la ejecución de esa consulta. **Imagina una CTE como una variable que almacena un resultado de consulta intermedio.** Este resultado puede ser utilizado en otras partes de la misma consulta, lo que permite escribir consultas más complejas y legibles. ### ¿Para qué sirven las CTE? * **Simplificar consultas complejas:** Dividiendo una consulta larga y complicada en partes más pequeñas y manejables. * **Reutilizar resultados intermedios:** Evitando tener que repetir subconsultas varias veces. * **Mejorar la legibilidad del código:** Al darle un nombre a un conjunto de resultados, el código se vuelve más fácil de entender y mantener. * **Implementar recursividad:** Las CTE se pueden utilizar para resolver problemas recursivos, como calcular jerarquías de datos. ### Estructura básica de una CTE SQL`WITH nombre_cte AS` ( ` -- Consulta que define la CTE` ) `SELECT * FROM` nombre\_cte; Usa el código [con precaución](/faq#coding). * **WITH:** Palabra clave para iniciar la definición de la CTE. * **nombre\_cte:** Un nombre que se le asigna a la CTE para poder referenciarla posteriormente. * **Consulta que define la CTE:** La consulta que produce el resultado que se almacenará en la CTE. ### Ejemplo práctico Supongamos que tenemos una tabla de empleados con una columna `manager_id` que indica el ID del jefe de cada empleado. Queremos encontrar a todos los empleados que reportan directamente o indirectamente a un determinado jefe. SQL`WITH Empleados_Jerarquia AS` ( ` SELECT` employee\_id, manager\_id ` FROM` employees ` WHERE manager_id = 123 -- ID del jefe al que queremos buscar reportes` ` UNION ALL` ` SELECT` e.employee\_id, e.manager\_id ` FROM` employees e ` INNER JOIN Empleados_Jerarquia eh ON e.manager_id =` eh.employee\_id ) `SELECT * FROM` Empleados\_Jerarquia; Usa el código [con precaución](/faq#coding). En este ejemplo: * La CTE `Empleados_Jerarquia` almacena inicialmente a todos los empleados que reportan directamente al jefe con ID 123. * Luego, se utiliza una unión recursiva (UNION ALL) para agregar a la CTE a todos los empleados que reportan a los empleados ya incluidos en la CTE. * Finalmente, se seleccionan todos los registros de la CTE para obtener la lista completa de empleados. ### Ventajas de usar CTE * **Legibilidad:** Las CTE hacen que el código sea más fácil de leer y entender. * **Reutilización:** Los resultados de una CTE se pueden reutilizar en múltiples partes de la consulta. * **Flexibilidad:** Las CTE se pueden utilizar en una amplia variedad de situaciones. * **Rendimiento:** En algunos casos, las CTE pueden mejorar el rendimiento de las consultas. **En resumen,** las CTE son una herramienta poderosa en SQL que te permite escribir consultas más complejas y eficientes. Al entender cómo funcionan y cuándo utilizarlas, puedes mejorar significativamente tus habilidades de escritura de SQL. **¿Te gustaría ver más ejemplos de cómo utilizar CTE en diferentes escenarios?** **Temas relacionados que podrías encontrar interesantes:** * **CTE recursivas:** Para resolver problemas jerárquicos. * **CTE en diferentes SGBD:** Pequeñas diferencias en la sintaxis entre MySQL, PostgreSQL, SQL Server, etc. * **CTE y rendimiento:** Consejos para optimizar el uso de CTE. FUENTE CHATGPT

Sería útil que los recursos incluyeran detalles sobre las columnas necesarias para crear la tabla. Siento que el curso fue elaborado con prisa al inicio; los recursos eran completos, pero ahora parece que faltan partes importantes.

Las CTE vienen siendo el equivalente a una funcion en programación

Nunca habia visto tanto desagrado en un curso, al parecer a todos nos desagradó este curso.

PESIMA CLASE , NO DAN LAS TABLAS BASE Y SALTAN MUCHO , NO ENTENDI NADA EN ESTA CLASE

Esto es super explicativo, de verdad se nota el potencial de CTE. 

Como en los recursos no dejan esa consulta ni nada con lo que podamos practicar. Dejo aquí un ejemplo simple de CTE usando la base de datos que ya tenemos: ```js WITH salario_promedio AS ( -- Subconsulta SELECT AVG(SALARY) AS promedio FROM instructors ) -- Consulta principal SELECT i.FIRSTNAME, i.LASTNAME, i.SALARY FROM instructors i, salario_promedio sp WHERE i.SALARY > sp.promedio; ```

**Procesos de manipulación de datos:** * **ETL**: Extract, Transform, and Load (Extracción, Transformación y Carga). * **ELT**: Extract, Load, and Transform (Extracción, Carga y Transformación). Durante el proceso de **Transformación**, se aplican actividades como: * **Depuración**: Eliminación de datos incorrectos o redundantes. * **Limpieza**: Asegurar que los datos sean consistentes y estén en un formato adecuado. * **Aplicación de reglas de negocio**: Ajustes y transformaciones basadas en las necesidades específicas del negocio. * **Generación de estadísticas**: Cálculo de métricas para análisis. * **Procedimientos**: Implementación de procesos o algoritmos específicos para preparar los datos. Este proceso a menudo se vuelve complejo debido a las **consultas avanzadas** que pueden requerirse. Aquí es donde entran en juego las **CTE (Common Table Expressions)**: * **CTE**: Las CTE nos permiten mejorar la legibilidad y mantenimiento del código SQL, facilitando su comprensión. Una CTE es básicamente una subconsulta que se define antes de la consulta principal. Esto nos permite dividir consultas complejas en bloques más manejables. Además, podemos encadenar una o más consultas en la consulta principal, lo que es útil para trabajar con datos temporales o complejos. Es similar a las funciones en lenguajes de programación, ya que descomponen tareas complicadas en partes más simples.

Este ejercicio si estuvo mas o menos. Que pena que no dejaron la BD para realizar este ejercicio. Termine haciendolo por medio de la BD que se trabajaba en el curso. Aqui les comparto mi codigo. ```js WITH AVERAGE_SALARY AS ( SELECT CONCAT(I.FIRSTNAME, ' ', I.LASTNAME) AS INSTRUCTOR_NAME, C.COURSE_NAME, C.DURATIONS_HOURS, AVG(I.SALARY) AS AVG_INSTRUCTOR_SALARY FROM INSTRUCTOR AS I JOIN COURSES AS C ON I.INSTRUCTORID = C.INSTRUCTORID GROUP BY INSTRUCTOR_NAME ) SELECT INSTRUCTOR_NAME, AVG_INSTRUCTOR_SALARY FROM AVERAGE_SALARY where AVG_INSTRUCTOR_SALARY > 55000; ```WITH AVERAGE\_SALARY AS ( SELECT CONCAT(I.FIRSTNAME, ' ', I.LASTNAME) AS INSTRUCTOR\_NAME, C.COURSE\_NAME, C.DURATIONS\_HOURS, AVG(I.SALARY) AS AVG\_INSTRUCTOR\_SALARY FROM INSTRUCTOR AS I JOIN COURSES AS C ON I.INSTRUCTORID = C.INSTRUCTORID GROUP BY INSTRUCTOR\_NAME ) SELECT INSTRUCTOR\_NAME, AVG\_INSTRUCTOR\_SALARY FROM AVERAGE\_SALARY where AVG\_INSTRUCTOR\_SALARY > 55000;

```js WITH cte_salary (salary) AS ( -- Subconsulta SELECT AVG(salary) AS 'AVG_SALARY' -- Define un nombre apropiado de la columna para mostrarlo en la salida FROM instructors ) -- Consulta principal que utiliza la CTE SELECT * FROM cte_salary WHERE salary > 'AVG_SALARY' ```WITH cte\_salary (salary) AS ( \-- Subconsulta SELECT AVG(salary) AS 'AVG\_SALARY' -- Define un nombre apropiado de la columna para mostrarlo en la salida FROM instructors ) \-- Consulta principal que utiliza la CTE SELECT \* FROM cte\_salary WHERE salary > 'AVG\_SALARY'

Segun lo que vi una consulta con varios CTE seria mas o menos asi ```js 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; ```

CTE = Common Table Expression Se usa la palabra reservada WITH para salvar subconsultas que seran usadas en la consulta principal.

\### Common Table Expressions (CTE) \> Para crear subconsultas que brinden información procesada para su próximo tratamiento. ```mysql WITH AverageSalaries AS ( SELECT d.DepartmentName, AVG(e.Salary) AS AvgSalary FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID GROUP BY d.DepartmentName ) SELECT DepartmentName, AvgSalary FROM AverageSalaries WHERE AvgSalary > 65000; ```

Siguiendo con la base de datos cursopracticosql, queria saber la cantidad de alumnos inscriptos por curso, además de su nombre, apellido y los nombres del curso, por eso realice dos subconsultas y las uní a través del inner join en la consulta principal ----> WITH cte\_count\_course AS ( SELECT studentid, COUNT(courseid) AS course\_count FROM student\_course GROUP BY studentid ), cte\_name\_course AS ( SELECT sc.studentid, c.course\_name FROM student\_course sc INNER JOIN courses c ON sc.courseid = c.courseid ) SELECT s.firstname, s.lastname, ccc.course\_count, cnc.course\_name FROM students AS s INNER JOIN cte\_count\_course AS ccc ON s.studentid = ccc.studentid INNER JOIN cte\_name\_course AS cnc ON s.studentid = cnc.studentid WHERE ccc.course\_count > 1 ORDER BY s.firstname;

CTE (Common Table Expression) permite definir una o más subconsultas con nombres temporales dentro de una consulta más grande. En ejemplo simple de sintaxis: WITH nombre\_cte (columna1, columna2, ...) AS ( \-- Definición de la subconsulta SELECT columna1, columna2, ... FROM tabla WHERE condiciones ) \-- Consulta principal que utiliza el CTE SELECT \* FROM nombre\_cte WHERE condiciones;

La mala práctica es que no se pueden mezclar mayúsculas con minúsculas: ```txt with averagesalaries AS ( select departmentname, avg(e.salary) as avgsalary from employees e JOIN departments d on d.departmentID = d.departmentID group by d.departmentname ) select departmentName, avgsalary from averagesalaries where avgsalary > 5500; ```

Cual seria un caso práctico real de los CTE? porque con el ejemplo, creo que funcionaria hacer la consulta normalmente.

Siguiendo con la base de datos de productos, este seria un ejemplo de CTE ```js WITH LOWERPRICE AS ( SELECT Products.PRODUCTNAME, Products.PRICE, Brands.BRANDNAME FROM TPRODUCTS AS Products INNER JOIN TBRANDS AS Brands ON Products.BRANDID = Brands.BRANDID WHERE Products.PRICE < 700 ) select * from LOWERPRICE; ```

La utilidad que veo de renombrar las tablas es cuando se tienen nombres extensos de tablas o propiedades y no queremos estar escribiendo ese nombre extenso lo usamos, siempre y cuando se fácil de entender a que nos estamos refiriendo

según lo que vi una consulta con varios CTE seria algo parecido a 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;

No encontré los recursos de la clase