Why CTEs matter in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a Common Table Expression (CTE) in PostgreSQL.

PostgreSQL

WITH [1] AS (SELECT * FROM employees)

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

Acte

BSELECT

CTABLE

DJOIN

Attempts:

3 left

💡 Hint

Common Mistakes

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

Forgetting to name the CTE.

The keyword WITH starts a CTE, and the name after it (like cte) defines the CTE's name.

2fill in blank

medium

Complete the code to select from a CTE named 'cte' in PostgreSQL.

PostgreSQL

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

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

A*

Bemployees

Ccte

Did, name

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the CTE name instead of columns.

Using '*' when specific columns are expected.

After defining the CTE, you select columns from it. Here, id, name are the columns to select.

3fill in blank

hard

Fix the error in the CTE usage by completing the missing keyword.

PostgreSQL

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

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

AAS

BFROM

CINTO

DJOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using FROM or INTO instead of AS.

Omitting the keyword entirely.

The keyword AS is required to define the CTE's query after its name and optional column list.

4fill in blank

hard

Fill both blanks to create a recursive CTE that counts down from 3 to 1.

PostgreSQL

WITH RECURSIVE countdown([1]) AS (SELECT 3 UNION ALL SELECT [2] - 1 FROM countdown WHERE [2] > 1) SELECT * FROM countdown;

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

An

Ccount

Dvalue

Attempts:

3 left

💡 Hint

Common Mistakes

Using different names for the column in the CTE and recursive SELECT.

Using SQL keywords as column names.

The recursive CTE uses the column name n and refers to it in the recursive SELECT.

5fill in blank

hard

Fill all three blanks to create a CTE that filters employees with salary over 50000 and selects their names and salaries.

PostgreSQL

WITH high_salary AS (SELECT [1], [2] FROM employees WHERE [3] > 50000) SELECT * FROM high_salary;

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

Aname

Bsalary

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting wrong columns like id instead of name.

Using incorrect column in WHERE clause.

The CTE selects name and salary columns, filtering where salary is greater than 50000.