CTEs, introduced in MySQL 8.0, allow for improved readability and modularity in SQL queries. A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. They are defined using the `WITH` keyword, followed by a query that generates a result set. CTEs can be non-recursive or recursive, providing solutions for complex data hierarchies and data transformations. This note covers the basics of CTEs in MySQL, emphasizing their syntax, benefits, and a simple usage example

Syntax:

WITH cte_name (column_list) AS (

    SELECT columns FROM table WHERE conditions

)

SELECT columns FROM cte_name WHERE conditions;

Benefits of Using CTEs:

1. Readability: Breaks down complex queries into simpler, more understandable parts.
2. Reusability: Allows the same CTE to be used multiple times within the same query.
3. Recursion: Recursive CTEs are useful for hierarchical or iterative data processing.
4. Temporary Data Storage: Acts as a temporary result set for calculations or transformations.

Example:

WITH sales_summary AS (

    SELECT salesperson_id, SUM(sales_amount) AS total_sales

    FROM sales

    GROUP BY salesperson_id

)

SELECT * FROM sales_summary WHERE total_sales > 10000;

In this example, the CTE `sales_summary` calculates the total sales per salesperson and is then used in the main query to filter results.

MODULE XXCI temp tables

Certainly! Here’s a brief note on temporary tables in MySQL:

Temporary Tables in MySQL

Temporary tables in MySQL are used to store intermediate results and perform complex operations. They are created using the `CREATE TEMPORARY TABLE` statement and are automatically dropped when the session that created them ends.

Syntax:

“`sql

CREATE TEMPORARY TABLE temp_table_name (

    column1 datatype,

    column2 datatype,

);

Key Features:

1. Session-Specific: The temporary table exists only for the duration of the session or connection that created it. Once the session is closed, the table is automatically dropped.

2. Data Isolation: Data in a temporary table is only visible to the session that created it, ensuring isolation from other sessions.

3. Performance: Temporary tables can improve performance by allowing complex calculations and data manipulations without affecting the main database tables.

4. Temporary Nature: Temporary tables do not require manual cleanup, as they are automatically deleted when the session ends.

Example Usage:

“`sql

CREATE TEMPORARY TABLE temp_sales (

    salesperson_id INT,

    total_sales DECIMAL(10, 2)

);

INSERT INTO temp_sales (salesperson_id, total_sales)

SELECT salesperson_id, SUM(sales_amount)

FROM sales

GROUP BY salesperson_id;

SELECT * FROM temp_sales WHERE total_sales > 10000;

“`

In this example, `temp_sales` is a temporary table used to store total sales by salesperson. It is populated with aggregated data and then queried for specific conditions.

– Temporary tables cannot have foreign key constraints.

– Indexes can be created on temporary tables to improve query performance.

– Use of temporary tables may consume additional memory, so it’s important to manage them efficiently.

!