WITH clause syntax in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a WITH clause in PostgreSQL.

PostgreSQL

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

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

AWITH

Btemp_table

CSELECT

DFROM

Attempts:

3 left

💡 Hint

Common Mistakes

Using SQL keywords like SELECT or FROM as the name.

Omitting the name after WITH.

The WITH clause defines a temporary named result set. You name it first, like temp_table.

2fill in blank

medium

Complete the code to define a WITH clause that selects all from the sales table.

PostgreSQL

WITH sales_data AS ([1] * FROM sales) SELECT * FROM sales_data;

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

AUPDATE

BINSERT

CSELECT

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT or UPDATE inside the WITH clause.

Omitting the SELECT keyword.

The WITH clause contains a subquery, so it must start with SELECT to retrieve data.

3fill in blank

hard

Fix the error in the WITH clause by choosing the correct keyword.

PostgreSQL

WITH recent_orders AS (SELECT * [1] orders WHERE order_date > '2024-01-01') SELECT * FROM recent_orders;

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

AFROM

BON

CJOIN

DINTO

Attempts:

3 left

💡 Hint

Common Mistakes

Using INTO instead of FROM.

Using JOIN or ON without a proper join clause.

In a SELECT statement, you specify the table with the FROM keyword.

4fill in blank

hard

Fill both blanks to create a WITH clause that calculates total sales per customer.

PostgreSQL

WITH total_sales AS (SELECT customer_id, SUM(amount) [1] sales [2] customer_id) SELECT * FROM total_sales;

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

AFROM

BWHERE

CGROUP BY

DORDER BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of GROUP BY for aggregation.

Omitting the FROM clause.

The SUM aggregation requires a FROM clause to specify the table, and a GROUP BY clause to group results by customer.

5fill in blank

hard

Fill all three blanks to write a WITH clause that finds customers with orders over 100, then selects their names.

PostgreSQL

WITH high_value_customers AS (SELECT customer_id [1] orders [2] amount > 100 GROUP BY customer_id [3] SUM(amount) > 100) SELECT name FROM customers WHERE id IN (SELECT customer_id FROM high_value_customers);

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

AFROM

BWHERE

CHAVING

DORDER BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using HAVING before WHERE.

Omitting the FROM clause.

Using ORDER BY instead of HAVING.

The subquery selects customer_id from orders (FROM), filters rows with WHERE, and uses HAVING to filter groups after aggregation.