Completar frases: Mastering SQL CTEs


Completar frases Mastering SQL CTEsVersión en línea Drills to master SQL Common Table Expressions por Good Sam 1 employees SELECT ALL IS manager_id INNER EmployeeHierarchy e UNION e.manager_id id AS NULL WITH name eh.level + 1 eh name eh.id level ON AS e.id FROM WHERE RECURSIVE SELECT manager_id id 0 FROM JOIN EmployeeHierarchy e.manager_id SELECT EmployeeHierarchy employees e.name FROM level Problem 1 : Recursive Employee Hierarchy Question : For a table employees ( id INT , name VARCHAR , manager_id INT ) , write a SQL query using a CTE to list all employees along with their level in the hierarchy ( 0 for the top manager , 1 for their direct reports , etc . ) . Solution : WITH RECURSIVE EmployeeHierarchy AS ( SELECT id , name , manager_id , 0 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e . id , e . name , e . manager_id , eh . level + 1 FROM employees e INNER JOIN EmployeeHierarchy eh ON e . manager_id = eh . id ) SELECT id , name , level FROM EmployeeHierarchy ; ( , , , , , , = ) , , ; Explanation : This recursive CTE starts with the top - level manager ( where manager_id is null ) and recursively joins to its own defined result set to navigate down the employee hierarchy , incrementing the level by one with each recursion . 2 ON e.id = ts.emp_id WITH SUM ts 0 total_sales BY GROUP FROM TotalSales total_sales sales AS AS SELECT sale_amount employees SELECT FROM AS e.name ts e emp_id TotalSales emp_id LEFT total_sales JOIN COALESCE Problem 2 : Summarizing Sales Question : Calculate the total sales for each employee , assuming tables employees ( id , name ) and sales ( emp_id , sale_amount ) . Solution : WITH TotalSales AS ( SELECT emp_id , SUM ( sale_amount ) AS total_sales FROM sales GROUP BY emp_id ) SELECT e . name , COALESCE ( ts . total_sales , 0 ) AS total_sales FROM employees e LEFT JOIN TotalSales ts ON e . id = ts . emp_id ; ( , ( ) ) , ( . , ) ; Explanation : The CTE TotalSales calculates the total sales per employee . The main query then joins this CTE with the employees table to display all employees , including those with no sales ( using COALESCE to handle NULLs ) . 3 ORDER SalesRank sr.rank e AS e.id = sr.emp_id rank SELECT DESC SalesRank AS WITH SELECT BY FROM sales BY sale_amount FROM emp_id employees ON sr e.name RANK() OVER JOIN emp_id GROUP SUM Problem 3 : Ranking Sales by Employee Question : Rank employees based on their total sales in descending order . Solution : ( , ( ( ) ) ) , ; WITH SalesRank AS ( SELECT emp_id , RANK ( ) OVER ( ORDER BY SUM ( sale_amount ) DESC ) AS rank FROM sales GROUP BY emp_id ) SELECT e . name , sr . rank FROM employees e JOIN SalesRank sr ON e . id = sr . emp_id ; Explanation : The CTE SalesRank calculates the total sales for each employee and assigns a rank based on these totals . The main query joins this CTE with the employees table to list employees along with their sales rank . 4 AS FROM AS month LAG sales FROM year FROM MonthlySales total_sales BY BY total_sales AS WITH SUM total_sales GROUP EXTRACT MonthlyGrowth OVER ORDER sale_date AS AS EXTRACT month MONTH FROM month FROM AS previous_month_sales growth FROM 0 EXTRACT YEAR SELECT total_sales SELECT MONTH sale_amount year FROM COALESCE year MonthlySales sale_date sale_date sale_date SELECT AS EXTRACT year previous_month_sales month total_sales MonthlyGrowth YEAR Problem 4 : Monthly Sales Analysis Question : Analyze monthly sales growth by comparing current month's sales with the previous month . Solution : ( ( ) , ( ) , ( ) ( ) , ( ) ) , ( , , , ( ) ( , ) ) , , , ( - , ) ; WITH MonthlySales AS ( SELECT EXTRACT ( YEAR FROM sale_date ) AS year , EXTRACT ( MONTH FROM sale_date ) AS month , SUM ( sale_amount ) AS total_sales FROM sales GROUP BY EXTRACT ( YEAR FROM sale_date ) , EXTRACT ( MONTH FROM sale_date ) ) , MonthlyGrowth AS ( SELECT year , month , total_sales , LAG ( total_sales ) OVER ( ORDER BY year , month ) AS previous_month_sales FROM MonthlySales ) SELECT year , month , total_sales , COALESCE ( total_sales - previous_month_sales , 0 ) AS growth FROM MonthlyGrowth ; Explanation : Two CTEs are used here . MonthlySales calculates the total sales for each month . MonthlyGrowth then uses the LAG window function within the CTE to find the previous month's sales , and the main query calculates the growth by subtracting the previous month's sales from the current month's . 5 3 AS JOIN e.id = esc.emp_id GROUP EmployeeSalesCount employees e num_sales avg_sale_amount BY COUNT() AS sales emp_id FROM esc SELECT HAVING esc WITH COUNT() > emp_id AVG FROM e.name avg_sale_amount ON EmployeeSalesCount sale_amount SELECT AS Problem 5 : Filtered Aggregation Question : Compute the average sales per employee only for those employees who made more than 3 sales . Solution : ( , , ( ) ) , . ; WITH EmployeeSalesCount AS ( SELECT emp_id , COUNT ( * ) AS num_sales , AVG ( sale_amount ) AS avg_sale_amount FROM sales GROUP BY emp_id HAVING COUNT ( * ) > 3 ) SELECT e . name , esc . avg_sale_amount FROM employees e JOIN EmployeeSalesCount esc ON e . id = esc . emp_id ; Explanation : The CTE EmployeeSalesCount calculates the number of sales and average sale amount per employee , filtering to include only those with more than three sales . The main query then joins this CTE to the employees table to fetch the employee names and their average sales . 6 EmployeeCTE name id FROM 'J%' employees EmployeeCTE SELECT WITH LIKE FROM name SELECT AS WHERE Problem 6 : Simple CTE Question : Create a CTE that selects all employees from the employees table and then use it to select all employees whose name starts with 'J' . Solution : ( , ) * ; WITH EmployeeCTE AS ( SELECT id , name FROM employees ) SELECT * FROM EmployeeCTE WHERE name LIKE 'J%' ; Explanation : This CTE , EmployeeCTE , acts as a temporary table containing all employee records . The main query then filters this CTE for employees whose names start with 'J' . 7 AS NumberSequence 1 number FROM AS 10 WITH RECURSIVE SELECT number SELECT UNION FROM ALL NumberSequence SELECT WHERE < NumberSequence number + 1 Problem 7 : Recursive CTE Question : Write a recursive CTE to generate a sequence of numbers from 1 to 10 . Solution : ( ) * ; WITH RECURSIVE NumberSequence AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM NumberSequence WHERE number < 10 ) SELECT * FROM NumberSequence ; Explanation : The recursive CTE starts with a base case ( selecting the number 1 ) , then recursively adds 1 to the previous number until the number 10 is reached . 8 running_total sale_date AS OVER SalesCTE FROM sales WITH SalesCTE amount BY amount AS sale_date ORDER SUM FROM SELECT SELECT Problem 8 : CTE for Running Total Question : Use a CTE to calculate the running total of sales from the sales table . Solution : WITH SalesCTE AS ( SELECT sale_date , amount , SUM ( amount ) OVER ( ORDER BY sale_date ) AS running_total FROM sales ) SELECT * FROM SalesCTE ; ( , , ( ) ( ) ) * ; Explanation : The CTE calculates the running total of sales amounts , ordered by the sale date . The window function SUM ( ) is used with the OVER clause to accumulate the sales . 9 FROM IN HighValueOrders WHERE order_id AS orders status = 'High value' WHERE > HighValueOrders 1000 order_id SELECT SELECT FROM UPDATE WITH order_id SET orders total_amount Problem 9 : CTE in UPDATE Statement Question : Use a CTE to update the status of orders in the orders table where the total amount is greater than 1000 . Solution : WITH HighValueOrders AS ( SELECT order_id FROM orders WHERE total_amount > 1000 ) UPDATE orders SET status = 'High value' WHERE order_id IN ( SELECT order_id FROM HighValueOrders ) ; ( ) ( ) ; Explanation : The CTE identifies orders with a total amount greater than 1000 . The main UPDATE query then uses this result to set the status of these orders . 10 SELECT salary ELSE WHEN BETWEEN SalaryCategories employees 'Low' WHEN salary THEN salary SELECT FROM 'Medium' AS WITH 'High' FROM END category 50000 SalaryCategories AS AND name THEN < id CASE 50000 100000 Problem 10 : CTE for Data Partitioning Question : Partition the employees table into three categories based on salary : Low , Medium , and High . Use a CTE for classification . Solution : WITH SalaryCategories AS ( SELECT id , name , salary , CASE WHEN salary < 50000 THEN 'Low' WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium' ELSE 'High' END AS category FROM employees ) SELECT * FROM SalaryCategories ; ( , , , ) * ; Explanation : The CTE classifies each employee into a salary category based on their salary . The CASE statement is used for conditional logic within the CTE to assign categories .

1

employees SELECT ALL IS manager_id INNER EmployeeHierarchy e UNION e.manager_id id AS NULL WITH name eh.level + 1 eh name eh.id level ON AS e.id FROM WHERE RECURSIVE SELECT manager_id id 0 FROM JOIN EmployeeHierarchy e.manager_id SELECT EmployeeHierarchy employees e.name FROM level

Problem 1 : Recursive Employee Hierarchy
Question : For a table employees ( id INT , name VARCHAR , manager_id INT ) , write a SQL query using a CTE to list all employees along with their level in the hierarchy ( 0 for the top manager , 1 for their direct reports , etc . ) .

Solution :

WITH RECURSIVE EmployeeHierarchy AS (
SELECT id , name , manager_id , 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e . id , e . name , e . manager_id , eh . level + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e . manager_id = eh . id
)
SELECT id , name , level
FROM EmployeeHierarchy ;

(
, , ,

, , ,

=
)
, ,
;

Explanation : This recursive CTE starts with the top - level manager ( where manager_id is null ) and recursively joins to its own defined result set to navigate down the employee hierarchy , incrementing the level by one with each recursion .

2

ON e.id = ts.emp_id WITH SUM ts 0 total_sales BY GROUP FROM TotalSales total_sales sales AS AS SELECT sale_amount employees SELECT FROM AS e.name ts e emp_id TotalSales emp_id LEFT total_sales JOIN COALESCE

Problem 2 : Summarizing Sales
Question : Calculate the total sales for each employee , assuming tables employees ( id , name ) and sales ( emp_id , sale_amount ) .

Solution :

WITH TotalSales AS (
SELECT emp_id , SUM ( sale_amount ) AS total_sales
FROM sales
GROUP BY emp_id
)
SELECT e . name , COALESCE ( ts . total_sales , 0 ) AS total_sales
FROM employees e
LEFT JOIN TotalSales ts ON e . id = ts . emp_id ;

(
, ( )

)
, ( . , )

;

Explanation : The CTE TotalSales calculates the total sales per employee . The main query then joins this CTE with the employees table to display all employees , including those with no sales ( using COALESCE to handle NULLs ) .

3

ORDER SalesRank sr.rank e AS e.id = sr.emp_id rank SELECT DESC SalesRank AS WITH SELECT BY FROM sales BY sale_amount FROM emp_id employees ON sr e.name RANK() OVER JOIN emp_id GROUP SUM

Problem 3 : Ranking Sales by Employee
Question : Rank employees based on their total sales in descending order .

Solution :

(
, ( ( ) )

)
,

;

WITH SalesRank AS (
SELECT emp_id , RANK ( ) OVER ( ORDER BY SUM ( sale_amount ) DESC ) AS rank
FROM sales
GROUP BY emp_id
)
SELECT e . name , sr . rank
FROM employees e
JOIN SalesRank sr ON e . id = sr . emp_id ;
Explanation : The CTE SalesRank calculates the total sales for each employee and assigns a rank based on these totals . The main query joins this CTE with the employees table to list employees along with their sales rank .

4

AS FROM AS month LAG sales FROM year FROM MonthlySales total_sales BY BY total_sales AS WITH SUM total_sales GROUP EXTRACT MonthlyGrowth OVER ORDER sale_date AS AS EXTRACT month MONTH FROM month FROM AS previous_month_sales growth FROM 0 EXTRACT YEAR SELECT total_sales SELECT MONTH sale_amount year FROM COALESCE year MonthlySales sale_date sale_date sale_date SELECT AS EXTRACT year previous_month_sales month total_sales MonthlyGrowth YEAR

Problem 4 : Monthly Sales Analysis
Question : Analyze monthly sales growth by comparing current month's sales with the previous month .

Solution :

(

( ) ,
( ) ,
( )

( ) , ( )
) ,
(

,
,
,
( ) ( , )

)
, , , ( - , )
;

WITH MonthlySales AS (
SELECT
EXTRACT ( YEAR FROM sale_date ) AS year ,
EXTRACT ( MONTH FROM sale_date ) AS month ,
SUM ( sale_amount ) AS total_sales
FROM sales
GROUP BY EXTRACT ( YEAR FROM sale_date ) , EXTRACT ( MONTH FROM sale_date )
) ,
MonthlyGrowth AS (
SELECT
year ,
month ,
total_sales ,
LAG ( total_sales ) OVER ( ORDER BY year , month ) AS previous_month_sales
FROM MonthlySales
)
SELECT year , month , total_sales , COALESCE ( total_sales - previous_month_sales , 0 ) AS growth
FROM MonthlyGrowth ;
Explanation : Two CTEs are used here . MonthlySales calculates the total sales for each month . MonthlyGrowth then uses the LAG window function within the CTE to find the previous month's sales , and the main query calculates the growth by subtracting the previous month's sales from the current month's .

5

3 AS JOIN e.id = esc.emp_id GROUP EmployeeSalesCount employees e num_sales avg_sale_amount BY COUNT() AS sales emp_id FROM esc SELECT HAVING esc WITH COUNT() > emp_id AVG FROM e.name avg_sale_amount ON EmployeeSalesCount sale_amount SELECT AS

Problem 5 : Filtered Aggregation
Question : Compute the average sales per employee only for those employees who made more than 3 sales .

Solution :

(
, , ( )

)
, .

;

WITH EmployeeSalesCount AS (
SELECT emp_id , COUNT ( * ) AS num_sales , AVG ( sale_amount ) AS avg_sale_amount
FROM sales
GROUP BY emp_id
HAVING COUNT ( * ) > 3
)
SELECT e . name , esc . avg_sale_amount
FROM employees e
JOIN EmployeeSalesCount esc ON e . id = esc . emp_id ;
Explanation : The CTE EmployeeSalesCount calculates the number of sales and average sale amount per employee , filtering to include only those with more than three sales . The main query then joins this CTE to the employees table to fetch the employee names and their average sales .

6

EmployeeCTE name id FROM 'J%' employees EmployeeCTE SELECT WITH LIKE FROM name SELECT AS WHERE

Problem 6 : Simple CTE
Question : Create a CTE that selects all employees from the employees table and then use it to select all employees whose name starts with 'J' .

Solution :

(
,

)
*

;

WITH EmployeeCTE AS (
SELECT id , name
FROM employees
)
SELECT *
FROM EmployeeCTE
WHERE name LIKE 'J%' ;
Explanation : This CTE , EmployeeCTE , acts as a temporary table containing all employee records . The main query then filters this CTE for employees whose names start with 'J' .

7

AS NumberSequence 1 number FROM AS 10 WITH RECURSIVE SELECT number SELECT UNION FROM ALL NumberSequence SELECT WHERE < NumberSequence number + 1

Problem 7 : Recursive CTE
Question : Write a recursive CTE to generate a sequence of numbers from 1 to 10 .

Solution :

(

)
*
;

WITH RECURSIVE NumberSequence AS (
SELECT 1 AS number
UNION ALL
SELECT number + 1
FROM NumberSequence
WHERE number < 10
)
SELECT *
FROM NumberSequence ;
Explanation : The recursive CTE starts with a base case ( selecting the number 1 ) , then recursively adds 1 to the previous number until the number 10 is reached .

8

running_total sale_date AS OVER SalesCTE FROM sales WITH SalesCTE amount BY amount AS sale_date ORDER SUM FROM SELECT SELECT

Problem 8 : CTE for Running Total
Question : Use a CTE to calculate the running total of sales from the sales table .

Solution :

WITH SalesCTE AS (
SELECT sale_date , amount ,
SUM ( amount ) OVER ( ORDER BY sale_date ) AS running_total
FROM sales
)
SELECT *
FROM SalesCTE ;

(
, ,
( ) ( )

)
*
;
Explanation : The CTE calculates the running total of sales amounts , ordered by the sale date . The window function SUM ( ) is used with the OVER clause to accumulate the sales .

9

FROM IN HighValueOrders WHERE order_id AS orders status = 'High value' WHERE > HighValueOrders 1000 order_id SELECT SELECT FROM UPDATE WITH order_id SET orders total_amount

Problem 9 : CTE in UPDATE Statement
Question : Use a CTE to update the status of orders in the orders table where the total amount is greater than 1000 .

Solution :

WITH HighValueOrders AS (
SELECT order_id
FROM orders
WHERE total_amount > 1000
)
UPDATE orders
SET status = 'High value'
WHERE order_id IN ( SELECT order_id FROM HighValueOrders ) ;

(

)

( ) ;
Explanation : The CTE identifies orders with a total amount greater than 1000 . The main UPDATE query then uses this result to set the status of these orders .

10

SELECT salary ELSE WHEN BETWEEN SalaryCategories employees 'Low' WHEN salary THEN salary SELECT FROM 'Medium' AS WITH 'High' FROM END category 50000 SalaryCategories AS AND name THEN < id CASE 50000 100000

Problem 10 : CTE for Data Partitioning
Question : Partition the employees table into three categories based on salary : Low , Medium , and High . Use a CTE for classification .

Solution :

WITH SalaryCategories AS (
SELECT id , name , salary ,
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'High'
END AS category
FROM employees
)
SELECT *
FROM SalaryCategories ;

(
, , ,

)
*
;
Explanation : The CTE classifies each employee into a salary category based on their salary . The CASE statement is used for conditional logic within the CTE to assign categories .

Mastering SQL CTEs

Usa estas credenciales para integrar el juego en un LMS compatible con LTI 1.1 o LTI 1.3 como Canvas, Moodle, o Blackboard. De esta manera podrás guardar las puntuaciones automáticamente en el libro de calificaciones de esa plataforma.

Mastering SQL CTEs