What is a Common Table Expression (CTE)? (A) An expression which consists of a temporary set of results defined in a SQL statement (B) A temporary table saved as a query (C) A query saved as a temporary table (D) A table stored as expression
Answer (1)
A Common Table Expression (CTE) is a feature in SQL (Structured Query Language) that allows you to define a temporary result set that can be used within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE is defined within the execution scope of a single SQL statement. The correct option for defining a CTE is (A) An expression which consists of a temporary set of results defined in a SQL statement.
Here's how a CTE works:
Definition: A CTE is typically defined using the WITH clause, followed by the name of the CTE. You'll then specify the SQL query that returns the result set.
Usage: Once the CTE is created, you can use it like a regular table or view within the SQL statement. It's like having a temporary set of data that you can reference multiple times in the same query.
Scopes: CTEs are useful in several scenarios, such as simplifying complex queries, breaking down queries into simpler parts, and improving code readability.
Example: Here’s a simple example of a CTE:
WITH SalesCTE AS ( SELECT employee_id, SUM(sales) AS TotalSales FROM sales_data GROUP BY employee_id ) SELECT employee_id, TotalSales FROM SalesCTE WHERE TotalSales > 1000;
In this example, SalesCTE is a CTE that calculates the total sales per employee. It is then used in the main query to select employees whose total sales exceed 1000.
CTEs are particularly favored in complex queries because they make it easier to understand the logical building blocks of a query without creating a permanent table.
