Scalar subqueries in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of scalar subquery in SELECT clause

Consider the following tables:

employees
id | name | department_id
1 | Alice | 10
2 | Bob | 20
3 | Carol | 10

departments
id | name
10 | Sales
20 | Marketing

What is the output of this query?

SELECT name, (SELECT name FROM departments WHERE id = department_id) AS dept_name FROM employees ORDER BY id;

MySQL

SELECT name, (SELECT name FROM departments WHERE id = department_id) AS dept_name FROM employees ORDER BY id;

A [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "Marketing"}, {"name": "Carol", "dept_name": "Sales"}]

B [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

C [{"name": "Alice", "dept_name": "Marketing"}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "Marketing"}]

D [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "Sales"}]

💡 Hint

Think about how the subquery uses the outer query's department_id to find the matching department name.

The scalar subquery selects the department name where the department id matches the employee's department_id. So Alice and Carol belong to Sales (id 10), Bob belongs to Marketing (id 20).

🧠 Conceptual

intermediate

Understanding scalar subquery behavior

Which of the following statements about scalar subqueries is true?

A A scalar subquery returns a table that can be joined directly in the FROM clause.

B A scalar subquery can return multiple columns but only one row without error.

C A scalar subquery can return multiple rows and columns and still be used in a SELECT clause.

D A scalar subquery must return exactly one row and one column; otherwise, it causes an error.

💡 Hint

Think about what happens if the subquery returns more than one value when used as a single value.

Scalar subqueries must return exactly one value (one row, one column). Returning more causes an error; returning zero rows returns NULL.

📝 Syntax

advanced

Identify the syntax error in scalar subquery usage

Which option contains a syntax error when using a scalar subquery in the WHERE clause?

MySQL

SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

A SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

B SELECT * FROM employees WHERE department_id = (SELECT id, name FROM departments WHERE name = 'Sales');

C SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

D SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' LIMIT 1);

💡 Hint

Remember scalar subqueries must return only one column.

Option B tries to return two columns (id, name) in a scalar subquery used with '=', which expects one value. This causes a syntax error.

❓ optimization

advanced

Optimizing scalar subqueries in SELECT

Given this query:

SELECT name, (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS avg_dept_salary FROM employees e;

Which optimization improves performance without changing the output?

A Rewrite using a JOIN with a GROUP BY to precompute average salaries per department.

B Add DISTINCT inside the scalar subquery to reduce duplicates.

C Replace the scalar subquery with a correlated subquery that uses LIMIT 1.

D Use a scalar subquery that selects salary instead of AVG(salary).

💡 Hint

Think about how many times the subquery runs and how to reduce repeated calculations.

Using a JOIN with GROUP BY computes average salaries once per department, avoiding repeated scalar subquery execution for each employee.

🔧 Debug

expert

Debugging unexpected NULL from scalar subquery

Given these tables:

employees
id | name | department_id
1 | Alice | 10
2 | Bob | 20
3 | Carol | 30

departments
id | name
10 | Sales
20 | Marketing

What is the output of this query?

SELECT name, (SELECT name FROM departments WHERE id = department_id) AS dept_name FROM employees ORDER BY id;

Why does Carol's dept_name show NULL?

A Because the subquery syntax is incorrect and always returns NULL.

B Because the scalar subquery returns multiple rows for Carol causing NULL to be assigned.

C Because there is no department with id 30 in the departments table, so the subquery returns no rows, resulting in NULL.

D Because the employees table has a NULL department_id for Carol.

💡 Hint

Check if the department_id for Carol exists in the departments table.

Carol's department_id is 30, which does not exist in departments, so the scalar subquery returns zero rows, which SQL treats as NULL.

Practice

1. What does a scalar subquery return in MySQL?

easy

A. A list of tables

B. A single value that can be used in another query

C. Only column names

D. Multiple rows and columns

Solution

1. Step 1: Understand scalar subquery definition

   A scalar subquery returns exactly one value, not multiple rows or columns.

2. Step 2: Identify usage in queries

   This single value can be used inside another query, like in WHERE or SELECT clauses.

3. Final Answer:

   A single value that can be used in another query -> Option B

4. Quick Check:

   Scalar subquery = single value [OK]

Hint: Scalar subqueries always return one value only [OK]

Common Mistakes:

• Thinking scalar subqueries return multiple rows
• Confusing scalar subqueries with table subqueries
• Assuming scalar subqueries return column names only

2. Which of the following is the correct syntax for a scalar subquery in a SELECT statement?

easy

A. SELECT name, MAX(score) FROM scores FROM students;

B. SELECT name, MAX(score) AS max_score WHERE score > 50;

C. SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students;

D. SELECT name, (MAX(score) FROM scores) AS max_score FROM students;

Solution

1. Step 1: Analyze correct scalar subquery syntax

   The scalar subquery must be enclosed in parentheses and return a single value, as in (SELECT MAX(score) FROM scores).

2. Step 2: Check placement in SELECT clause

   SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; correctly places the scalar subquery inside the SELECT list with an alias.

3. Final Answer:

   SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; -> Option C

4. Quick Check:

   Scalar subquery syntax = SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; [OK]

Hint: Scalar subqueries must be in parentheses inside SELECT or WHERE [OK]

Common Mistakes:

• Missing parentheses around subquery
• Incorrect FROM clause placement
• Using aggregate functions without subquery

3. Given the tables:
students(id, name)
scores(student_id, score)
What will this query return?

SELECT name, (SELECT AVG(score) FROM scores WHERE student_id = students.id) AS avg_score FROM students;

medium

A. Each student's name with their average score

B. The overall average score for all students repeated for each name

C. Syntax error due to subquery

D. List of student names only without scores

Solution

1. Step 1: Understand the subquery correlation

   The subquery calculates the average score for each student by matching student_id with students.id.

2. Step 2: Result of the main query

   The main query returns each student's name along with their individual average score from the subquery.

3. Final Answer:

   Each student's name with their average score -> Option A

4. Quick Check:

   Scalar subquery returns per-student average [OK]

Hint: Correlated scalar subqueries return values per row [OK]

Common Mistakes:

• Assuming subquery returns overall average for all rows
• Confusing correlated and uncorrelated subqueries
• Expecting syntax error when none exists

4. Identify the error in this query using a scalar subquery:

SELECT name, (SELECT score FROM scores) AS score FROM students;

medium

A. No error, query runs fine

B. Missing alias for subquery

C. Incorrect table name in subquery

D. Subquery returns multiple rows causing an error

Solution

1. Step 1: Analyze the subquery result

   The subquery SELECT score FROM scores returns multiple rows because no WHERE clause limits it.

2. Step 2: Understand scalar subquery requirement

   Scalar subqueries must return exactly one value; multiple rows cause an error.

3. Final Answer:

   Subquery returns multiple rows causing an error -> Option D

4. Quick Check:

   Scalar subquery must return one value [OK]

Hint: Scalar subqueries must return one value, not many [OK]

Common Mistakes:

• Ignoring multiple rows returned by subquery
• Assuming alias is mandatory for subquery
• Thinking query runs without error

5. You want to find students whose score is above the average score of all students. Which query correctly uses a scalar subquery to achieve this?

hard

A. SELECT name FROM students WHERE score > (SELECT AVG(score) FROM scores);

B. SELECT name FROM students WHERE score > SELECT AVG(score) FROM scores;

C. SELECT name FROM students WHERE (SELECT AVG(score) FROM scores) < (SELECT score FROM scores WHERE student_id = students.id);

D. SELECT name FROM students WHERE score > (SELECT score FROM scores);

Solution

1. Step 1: Understand the goal

   We want students whose individual score is greater than the average score of all students.

2. Step 2: Analyze each option

   SELECT name FROM students WHERE score > (SELECT AVG(score) FROM scores); correctly compares each student's score with the average score using a scalar subquery.

3. Final Answer:

   SELECT name FROM students WHERE score > (SELECT AVG(score) FROM scores); -> Option A

4. Quick Check:

   Scalar subquery compares average with individual score [OK]

Hint: Use scalar subquery for average, correlate for individual score [OK]

Common Mistakes:

• Missing parentheses around subqueries
• Using subquery without correlation for individual scores
• Syntax errors from missing SELECT in WHERE