CTE materialization behavior in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a CTE named 'cte_example' that selects all columns from 'employees'.

PostgreSQL

WITH cte_example AS (SELECT * FROM [1]) SELECT * FROM cte_example;

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

Aemployees

Bdepartments

Csalaries

Dprojects

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a table unrelated to employees like 'departments'.

Using a table that doesn't exist in the database.

The CTE 'cte_example' selects all columns from the 'employees' table. Using 'employees' in the FROM clause is correct.

2fill in blank

medium

Complete the code to prevent CTE materialization by using the 'MATERIALIZED' or 'NOT MATERIALIZED' keyword.

PostgreSQL

WITH cte_data AS [1] (SELECT id, name FROM employees) SELECT * FROM cte_data;

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

ANOT MATERIALIZED

BRECURSIVE

CTEMPORARY

DMATERIALIZED

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'MATERIALIZED' which forces materialization.

Using unrelated keywords like 'TEMPORARY' or 'RECURSIVE'.

Using 'NOT MATERIALIZED' tells PostgreSQL to inline the CTE instead of materializing it.

3fill in blank

hard

Fix the error in the query by choosing the correct keyword to force materialization of the CTE.

PostgreSQL

WITH cte_sales AS [1] (SELECT * FROM sales WHERE amount > 100) SELECT * FROM cte_sales;

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

ATEMPORARY

BMATERIALIZED

CNOT MATERIALIZED

DRECURSIVE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'NOT MATERIALIZED' which disables materialization.

Using unrelated keywords like 'TEMPORARY' or 'RECURSIVE'.

The 'MATERIALIZED' keyword forces PostgreSQL to store the CTE result temporarily, which can be useful for performance reasons.

4fill in blank

hard

Fill both blanks to write a CTE that is not materialized and filters employees with salary greater than 50000.

PostgreSQL

WITH cte_high_salary AS [1] (SELECT * FROM employees WHERE salary [2] 50000) SELECT * FROM cte_high_salary;

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

ANOT MATERIALIZED

B>

C<

DMATERIALIZED

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'MATERIALIZED' instead of 'NOT MATERIALIZED'.

Using '<' instead of '>' for filtering salaries.

The CTE is declared as 'NOT MATERIALIZED' to avoid storing intermediate results. The filter uses '>' to select salaries greater than 50000.

5fill in blank

hard

Fill all three blanks to create a materialized CTE that selects project names and filters projects started after 2020.

PostgreSQL

WITH cte_projects AS [1] (SELECT name AS [2] FROM projects WHERE start_date [3] '2020-12-31') SELECT * FROM cte_projects;

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

AMATERIALIZED

Bproject_name

C>

DNOT MATERIALIZED

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'NOT MATERIALIZED' instead of 'MATERIALIZED'.

Not renaming the column properly.

Using '<' instead of '>' for date comparison.

The CTE is forced to materialize with 'MATERIALIZED'. The column 'name' is renamed to 'project_name'. The filter uses '>' to select projects started after December 31, 2020.