Why subqueries are needed in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select employees who earn more than the average salary.

SQL

SELECT name FROM employees WHERE salary > (SELECT [1] FROM employees);

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

AMIN(salary)

BMAX(salary)

CAVG(salary)

DCOUNT(salary)

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX() instead of AVG() in the subquery.

Forgetting to use a subquery for comparison.

The subquery calculates the average salary. The main query selects employees earning more than this average.

2fill in blank

medium

Complete the code to find products with prices higher than the average price.

SQL

SELECT product_name FROM products WHERE price > (SELECT [1] FROM products);

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

AAVG(price)

BSUM(price)

CMIN(price)

DCOUNT(price)

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM() instead of AVG() in the subquery.

Not using parentheses for the subquery.

The subquery calculates the average price. The main query selects products priced above this average.

3fill in blank

hard

Fix the error in the query to select customers who placed orders with amounts greater than the average order amount.

SQL

SELECT customer_id FROM orders WHERE order_amount > (SELECT [1](order_amount) FROM orders);

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

AMAX

BAVG

CSUM

DMIN

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the aggregate function.

Using SUM or MAX instead of AVG.

The subquery needs the AVG function to calculate the average order amount. Without AVG, the query is invalid.

4fill in blank

hard

Fill both blanks to select employees whose salary is greater than the average salary in their department.

SQL

SELECT name FROM employees WHERE salary > (SELECT [1] FROM employees WHERE department_id = [2]);

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

AAVG(salary)

BMAX(salary)

Cdepartment_id

DMIN(salary)

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX instead of AVG for salary.

Using wrong column for department filter.

The subquery calculates the average salary for the employee's department. The main query compares each employee's salary to this average.

5fill in blank

hard

Fill all three blanks to select products with price greater than the average price in their category and category matches the main query.

SQL

SELECT product_name FROM products WHERE price > (SELECT [1] FROM products WHERE category_id = [2]) AND category_id = [3];

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

AAVG(price)

Bcategory_id

DMAX(price)

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX(price) instead of AVG(price).

Not matching category_id in both places.

The subquery calculates the average price for the category. The main query selects products priced above this average and in the same category.