CTE Introduction Flashcards
(20 cards)
What does CTE stand for in SQL?
Common Table Expression
What is a Common Table Expression?
A named temporary result set used within a SQL statement.
Which SQL keyword is used to create a CTE?
WITH
In which types of SQL statements can you use a CTE?
SELECT, INSERT, UPDATE, DELETE
Does a CTE persist after the query is run?
No, it is temporary and discarded after the query.
How is a CTE referenced in the main query?
Like a regular table, using its assigned name.
What is one benefit of using a CTE over creating a physical table?
You avoid the need to create and drop temporary tables.
What is the syntax structure after the WITH keyword?
CTE name, followed by a SELECT statement inside parentheses.
Can you use more than one CTE in a query?
Yes, by separating them with commas after a single WITH keyword.
How are multiple CTEs referenced in the main query?
By their individual names as if they were tables.
What does the example with schools calculate?
Average number of students per district.
In the banking bonus example, what does the first CTE calculate?
Average bonus for each position.
When joining a table with a CTE, which clause is typically used?
JOIN
What is an advantage of using CTEs for readability?
They organize complex queries into logical building blocks.
Can a CTE reference another CTE?
Yes, in the case of nested CTEs.
What is a nested CTE?
A CTE that uses another CTE within its definition.
In the bonus example with multiple CTEs, what two averages are compared?
Average bonus per position and per region.
What SQL aggregate functions were demonstrated with CTEs in examples?
AVG, MIN, MAX
In the outlet bonus evaluation, which CTE calculates the average bonus per outlet?
avg_per_outlet
What should you be careful about when nesting CTEs?
Ensure earlier CTEs are defined before referencing them.
