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).

Practice

1. What is a subquery in a database query?

easy

A. A query nested inside another query

B. A query that updates data

C. A query that deletes tables

D. A query that creates indexes

Solution

1. Step 1: Understand the definition of subquery

   A subquery is a query written inside another query to help filter or calculate data.

2. Step 2: Identify the correct description

   Among the options, only "A query nested inside another query" matches the definition of a subquery.

3. Final Answer:

   A query nested inside another query -> Option A

4. Quick Check:

   Subquery = Nested query [OK]

Hint: Subquery means query inside another query [OK]

Common Mistakes:

• Confusing subquery with update or delete operations
• Thinking subquery creates indexes
• Mixing subquery with table creation

2. Which of the following is the correct syntax to use a subquery in the WHERE clause?

easy

A. SELECT * FROM table WHERE column = (SELECT value FROM other_table);

B. SELECT * FROM table WHERE SELECT value FROM other_table = column;

C. SELECT * FROM table WHERE column IN SELECT value FROM other_table;

D. SELECT * FROM table WHERE column = SELECT value FROM other_table;

Solution

1. Step 1: Review correct subquery syntax in WHERE clause

   The subquery must be enclosed in parentheses and used as a value to compare with a column.

2. Step 2: Check each option

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); correctly uses parentheses around the subquery. Options A, B, and D have syntax errors or missing parentheses.

3. Final Answer:

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); -> Option A

4. Quick Check:

   Subquery in WHERE uses parentheses [OK]

Hint: Subquery in WHERE must be inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing subquery before column
• Using IN without parentheses

3. Consider the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What does this query return?

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in Sales

B. Names of all employees regardless of department

C. Names of employees who work in the Sales department

D. Department names of all employees

Solution

1. Step 1: Understand the subquery

   The subquery finds the dept_id for the department named 'Sales'.

2. Step 2: Apply the subquery result in the main query

   The main query selects employee names whose dept_id matches the Sales dept_id.

3. Final Answer:

   Names of employees who work in the Sales department -> Option C

4. Quick Check:

   Subquery filters Sales dept_id, main query filters employees [OK]

Hint: Subquery finds Sales dept_id, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Ignoring subquery filtering

4. Identify the error in this query:

SELECT name FROM Employees WHERE dept_id = SELECT dept_id FROM Departments WHERE dept_name = 'HR';

medium

A. Wrong table name used

B. Missing parentheses around the subquery

C. Using = instead of IN

D. Missing WHERE clause in subquery

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses when used in WHERE clause comparisons.

2. Step 2: Identify the error in the query

   The query misses parentheses around the subquery, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subquery needs parentheses [OK]

Hint: Always put subqueries in parentheses [OK]

Common Mistakes:

• Omitting parentheses
• Confusing = with IN unnecessarily
• Incorrect table names

5. You want to find all employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT emp_name FROM Employees WHERE salary > (SELECT MAX(salary) FROM Employees);

B. SELECT emp_name FROM Employees WHERE salary > AVG(salary);

C. SELECT emp_name FROM Employees WHERE salary > (SELECT salary FROM Employees);

D. SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id);

Solution

1. Step 1: Understand the requirement

   We need to compare each employee's salary to the average salary of their own department.

2. Step 2: Analyze each option

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); uses a correlated subquery that calculates the average salary for the employee's department. Options A, B, and C do not correctly calculate the average per department or use wrong aggregates.

3. Final Answer:

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); -> Option D

4. Quick Check:

   Correlated subquery compares salary to dept average [OK]

Hint: Use correlated subquery with dept_id to get average salary [OK]

Common Mistakes:

• Using global average instead of per department
• Using MAX instead of AVG
• Not correlating subquery with outer query