Multiple CTEs in one query in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a CTE named 'first_cte'.

SQL

WITH [1] AS (SELECT * FROM employees)

Drag options to blanks, or click blank then click option'

ASELECT

Bemployees

CFROM

Dfirst_cte

Attempts:

3 left

💡 Hint

Common Mistakes

Using SQL keywords like SELECT or FROM as the CTE name.

Skipping the CTE name after WITH.

The WITH clause starts a CTE, and you must name it. Here, 'first_cte' is the correct CTE name.

2fill in blank

medium

Complete the code to add a second CTE named 'second_cte' after the first one.

SQL

WITH first_cte AS (SELECT * FROM employees), [1] AS (SELECT * FROM departments)

Drag options to blanks, or click blank then click option'

Adepartments

Bsecond_cte

Cemployees

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using table names like 'departments' as CTE names.

Forgetting the comma between CTEs.

When defining multiple CTEs, separate them with commas and give each a unique name. Here, 'second_cte' is the correct name for the second CTE.

3fill in blank

hard

Fix the error in the query by completing the code to select from both CTEs.

SQL

WITH first_cte AS (SELECT id, name FROM employees), second_cte AS (SELECT id, dept FROM departments) SELECT f.name, s.dept FROM [1] f JOIN second_cte s ON f.id = s.id;

Drag options to blanks, or click blank then click option'

Afirst_cte

Bsecond_cte

Cemployees

Ddepartments

Attempts:

3 left

💡 Hint

Common Mistakes

Using original table names instead of CTE names in the main query.

Mixing up the CTE names.

The main query must select from the CTE names, not the original tables. Here, 'first_cte' is the correct CTE to join with 'second_cte'.

4fill in blank

hard

Fill both blanks to create two CTEs and select from them.

SQL

WITH [1] AS (SELECT id, salary FROM employees), [2] AS (SELECT id, bonus FROM bonuses) SELECT e.salary, b.bonus FROM e JOIN b ON e.id = b.id;

Drag options to blanks, or click blank then click option'

Ae

Bemployees

Cb

Dbonuses

Attempts:

3 left

💡 Hint

Common Mistakes

Using table names instead of CTE names.

Not matching CTE names with aliases in the main query.

CTEs can be named anything. Here, 'e' and 'b' are short names for the CTEs, which are then used in the main query.

5fill in blank

hard

Fill all three blanks to define two CTEs and select the combined result.

SQL

WITH [1] AS (SELECT id, price FROM products), [2] AS (SELECT product_id, quantity FROM sales) SELECT p.price, s.quantity FROM [3] p JOIN sales s ON p.id = s.product_id;

Drag options to blanks, or click blank then click option'

Aproducts

Bsales

Dproduct_sales

Attempts:

3 left

💡 Hint

Common Mistakes

Using table names instead of CTE names.

Mismatching aliases and CTE names.

The first CTE is named 'products', the second 'sales'. The main query selects from 'products' aliased as 'p' and the 'sales' CTE.