Subqueries and nested queries in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all employees who work in the department with the highest budget.

DBMS Theory

SELECT * FROM employees WHERE department_id = (SELECT [1] FROM departments ORDER BY budget DESC LIMIT 1);

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

Aid

Bbudget

Cdepartment_id

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing the budget column instead of the department id in the subquery.

Using a column that does not uniquely identify the department.

The subquery selects the id of the department with the highest budget. The main query then selects employees working in that department.

2fill in blank

medium

Complete the code to find products priced higher than the average price.

DBMS Theory

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

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

AMAX

BMIN

CAVG

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX or MIN instead of AVG.

Using SUM which returns total, not average.

The subquery calculates the average price using AVG(price). The main query selects products priced above this average.

3fill in blank

hard

Fix the error in the subquery to find customers who placed orders totaling more than the highest order total among orders over $1000.

DBMS Theory

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

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

Aorder_total

BAVG(order_total)

CSUM(order_total)

DMAX(order_total)

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name without aggregation causing multiple rows error.

Using SUM which sums all orders instead of a single value.

The subquery must return a single value. Using MAX(order_total) returns the highest order total over $1000, which can be compared correctly.

4fill in blank

hard

Fill both blanks to select employees who earn more than the average salary in their department.

DBMS Theory

SELECT employee_name FROM employees e1 WHERE salary > (SELECT [1](salary) FROM employees e2 WHERE e2.department_id = e1.[2]);

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

AAVG

BMAX

Cdepartment_id

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX instead of AVG for average salary.

Using employee_id instead of department_id in the subquery.

The subquery calculates the average salary (AVG(salary)) for the employee's department (department_id), allowing comparison to find employees earning more.

5fill in blank

hard

Fill the blanks to create a subquery that lists products with sales greater than the average sales in their category.

DBMS Theory

SELECT product_name FROM products p1 WHERE sales > (SELECT [1](sales) FROM products p2 WHERE p2.category_id = p1.[2]);

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

AAVG

Bcategory_id

Cproduct_id

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX instead of AVG for average sales.

The subquery calculates the average sales (AVG(sales)) for the product's category (category_id).