CTE vs subquery vs view decision in SQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a CTE named recent_orders.

SQL

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

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

Arecent_orders

Borders_recent

Corders_cte

Drecent

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different name in the CTE definition and the SELECT statement.

Using a name that is not descriptive or confusing.

The CTE name must match the name used after WITH and in the main query. 'recent_orders' is the correct name here.

2fill in blank

medium

Complete the code to write a subquery that selects customers with orders over 100.

SQL

SELECT customer_id FROM customers WHERE customer_id IN (SELECT [1] FROM orders WHERE amount > 100);

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

Aamount

Border_id

Ccustomer_id

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting order_id instead of customer_id in the subquery.

Selecting amount or order_date which do not match the outer query condition.

The subquery must select customer_id to match the outer query's WHERE condition.

3fill in blank

hard

Fix the error in the view creation statement by filling the blank.

SQL

CREATE VIEW [1] AS SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id;

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

Asales_summary

Bsummary_sales

Csales_total

Dproduct_sales

Attempts:

3 left

💡 Hint

Common Mistakes

Using a name that does not reflect the data.

Using reserved keywords or invalid characters in the view name.

The view name should be descriptive and consistent. 'sales_summary' clearly describes the content.

4fill in blank

hard

Fill both blanks to write a query using a CTE and filter results.

SQL

WITH high_sales AS (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) SELECT * FROM high_sales WHERE total [1] [2];

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

A>

B1000

C<

D500

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' to filter high sales.

Using a low threshold that does not match the intent.

The query filters customers with total sales greater than 1000.

5fill in blank

hard

Fill all three blanks to create a view that shows average order amount per customer with a filter.

SQL

CREATE VIEW [1] AS SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY [2] HAVING AVG(amount) [3] 50;

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

Acustomer_avg_order

Bcustomer_id

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong grouping column.

Using '<' instead of '>' in HAVING clause.

Choosing a view name that is unclear.

The view name is 'customer_avg_order', grouping is by 'customer_id', and the HAVING clause filters averages greater than 50.