Scalar subquery in SELECT in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of scalar subquery in SELECT clause

Given the tables Employees and Departments, what is the output of the following query?

SELECT e.EmployeeID, e.Name, (SELECT d.DepartmentName FROM Departments d WHERE d.DepartmentID = e.DepartmentID) AS DeptName FROM Employees e WHERE e.EmployeeID = 3;

SQL

SELECT e.EmployeeID, e.Name, (SELECT d.DepartmentName FROM Departments d WHERE d.DepartmentID = e.DepartmentID) AS DeptName FROM Employees e WHERE e.EmployeeID = 3;

A []

B [{"EmployeeID": 3, "Name": "Alice", "DeptName": null}]

C [{"EmployeeID": 3, "Name": "Alice", "DeptName": "Sales"}]

D SyntaxError

💡 Hint

Think about how the scalar subquery returns a single value for each row in Employees.

The scalar subquery fetches the DepartmentName from Departments where DepartmentID matches the employee's DepartmentID. For EmployeeID 3, the department is Sales, so DeptName is 'Sales'.

🧠 Conceptual

intermediate

Understanding scalar subquery behavior

What happens if a scalar subquery in the SELECT clause returns more than one row?

A The query runs successfully and returns the first value only.

B The query raises an error because scalar subqueries must return exactly one value.

C The query concatenates all returned values into a string.

D The query returns NULL for that column.

💡 Hint

Scalar means a single value, not multiple.

A scalar subquery must return exactly one value. If it returns more than one row, the database raises an error.

📝 Syntax

advanced

Identify the syntax error in scalar subquery usage

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

SQL

SELECT e.EmployeeID, (SELECT DepartmentName FROM Departments WHERE DepartmentID = e.DepartmentID) AS DeptName FROM Employees e;

A SELECT e.EmployeeID, (SELECT DepartmentName FROM Departments WHERE DepartmentID = e.DepartmentID) DeptName FROM Employees;

B SELECT e.EmployeeID, (SELECT DepartmentName FROM Departments d WHERE d.DepartmentID = e.DepartmentID) DeptName FROM Employees e;

C SELECT e.EmployeeID, (SELECT DepartmentName FROM Departments WHERE DepartmentID = e.DepartmentID) AS DeptName FROM Employees e;

D SELECT e.EmployeeID, (SELECT DepartmentName FROM Departments WHERE DepartmentID = e.DepartmentID) AS DeptName FROM Employees;

💡 Hint

Check if table aliases are consistent and if all tables are referenced properly.

Option A references 'e.DepartmentID' but does not alias Employees as 'e', causing a syntax error.

❓ optimization

advanced

Optimizing scalar subquery in SELECT

Which query is more efficient when retrieving employee names with their department names?

A SELECT e.Name, d.DepartmentName FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID;

B SELECT e.Name, (SELECT d.DepartmentName FROM Departments d WHERE d.DepartmentID = e.DepartmentID) AS DeptName FROM Employees e;

C SELECT e.Name, d.DepartmentName FROM Employees e, Departments d WHERE e.DepartmentID = d.DepartmentID;

D SELECT e.Name, (SELECT DepartmentName FROM Departments WHERE DepartmentID = e.DepartmentID LIMIT 1) AS DeptName FROM Employees e;

💡 Hint

Consider how joins and subqueries affect performance.

Using a JOIN (option A) is generally more efficient than a scalar subquery for each row because it avoids repeated subquery execution.

🔧 Debug

expert

Debugging unexpected NULL from scalar subquery

Given the query:

SELECT e.EmployeeID, e.Name, (SELECT d.DepartmentName FROM Departments d WHERE d.DepartmentID = e.DepartmentID) AS DeptName FROM Employees e;

Why might DeptName be NULL for some employees even though their DepartmentID exists in Departments?

A The scalar subquery returns multiple rows for some employees, causing NULL to be returned.

B The scalar subquery is not allowed in SELECT clause and returns NULL by default.

C The Departments table has duplicate DepartmentID values causing the subquery to fail silently.

D Some DepartmentID values in Employees do not exactly match any DepartmentID in Departments due to data type mismatch or trailing spaces.

💡 Hint

Check data consistency and matching conditions between tables.

If DepartmentID values do not match exactly due to data type differences or extra spaces, the subquery returns no rows, resulting in NULL.

Practice

1. What does a scalar subquery in the SELECT clause return?

easy

A. Only column names

B. Multiple rows and columns

C. A single value (one row, one column)

D. Only table names

Solution

1. Step 1: Understand scalar subquery definition

   A scalar subquery returns exactly one value, meaning one row and one column.

2. Step 2: Compare with other subquery types

   Unlike table subqueries, scalar subqueries cannot return multiple rows or columns.

3. Final Answer:

   A single value (one row, one column) -> Option C

4. Quick Check:

   Scalar subquery = single value [OK]

Hint: Scalar subquery returns one value only, not a table [OK]

Common Mistakes:

• Thinking scalar subquery returns multiple rows
• Confusing scalar subquery with table subquery
• Assuming scalar subquery returns column names only

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

easy

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

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

C. SELECT name, 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: Identify correct scalar subquery syntax

   The scalar subquery must be enclosed in parentheses and used inside the SELECT clause.

2. Step 2: Check each option

   SELECT name, (SELECT MAX(score) FROM scores) AS max_score FROM students; correctly uses parentheses around the subquery. Others miss parentheses or have wrong placement.

3. Final Answer:

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

4. Quick Check:

   Scalar subquery syntax = parentheses [OK]

Hint: Always put scalar subquery inside parentheses in SELECT [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing SELECT keyword incorrectly
• Using aggregate functions without subquery

3. Given tables employees(id, name, dept_id) and departments(id, dept_name), what is the output of this query?

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

medium

A. Syntax error due to subquery

B. [{"name": "Alice", "department": null}, {"name": "Bob", "department": null}]

C. [{"name": "Alice", "department": "IT"}, {"name": "Bob", "department": "HR"}]

D. [{"name": "Alice", "department": "HR"}, {"name": "Bob", "department": "IT"}]

Solution

1. Step 1: Understand query logic

   For each employee, the scalar subquery fetches the department name matching their dept_id.

2. Step 2: Match employees to departments

   Alice's dept_id matches HR, Bob's matches IT, so the output shows correct department names.

3. Final Answer:

   [{"name": "Alice", "department": "HR"}, {"name": "Bob", "department": "IT"}] -> Option D

4. Quick Check:

   Scalar subquery returns matching department name per employee [OK]

Hint: Scalar subquery returns one value per row, matching join logic [OK]

Common Mistakes:

• Assuming subquery returns multiple rows causing error
• Mixing up department names for employees
• Expecting nulls when matching keys exist

4. Identify the error in this query:

SELECT name, (SELECT dept_name FROM departments WHERE id = employees.dept_id) AS department FROM employees WHERE (SELECT COUNT(*) FROM departments) > 0;

medium

A. Scalar subquery in WHERE is valid but inefficient

B. Scalar subquery in WHERE returns multiple rows

C. Missing alias for subquery in SELECT

D. Subquery in WHERE must return a single value

Solution

1. Step 1: Analyze subquery in WHERE clause

   The subquery in WHERE returns COUNT(*), which is a single value, so it's valid.

2. Step 2: Consider efficiency and logic

   Using a scalar subquery in WHERE like this works but is inefficient; better to check existence differently.

3. Final Answer:

   Scalar subquery in WHERE is valid but inefficient -> Option A

4. Quick Check:

   Scalar subquery in WHERE can be valid but watch efficiency [OK]

Hint: Scalar subquery in WHERE must return one value; check efficiency [OK]

Common Mistakes:

• Thinking scalar subquery in WHERE always causes error
• Confusing alias requirement in SELECT with WHERE
• Assuming subquery returns multiple rows here

5. You want to list all products with their category name, but some products have no category assigned (category_id is NULL). Which query correctly uses a scalar subquery in SELECT to show category names or 'Uncategorized' if none?

Options:
A) SELECT product_name, IFNULL((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products WHERE category_id IS NOT NULL;
B) SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' AS category FROM products;
C) SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) AS category FROM products WHERE category_id IS NOT NULL;
D) SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products;

hard

A. SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products;

B. SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' AS category FROM products;

C. SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) AS category FROM products WHERE category_id IS NOT NULL;

D. SELECT product_name, IFNULL((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products WHERE category_id IS NOT NULL;

Solution

1. Step 1: Handle NULL category_id with scalar subquery

   Use COALESCE to replace NULL result from subquery with 'Uncategorized'.

2. Step 2: Check each option's correctness

   The query with COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products; correctly uses COALESCE and includes all products. The query with (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' uses invalid OR syntax. The queries with WHERE category_id IS NOT NULL exclude products with NULL category_id.

3. Final Answer:

   SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products; -> Option A

4. Quick Check:

   Use COALESCE with scalar subquery for NULL handling [OK]

Hint: Use COALESCE to handle NULL from scalar subquery [OK]

Common Mistakes:

• Using OR instead of COALESCE or IFNULL
• Filtering out NULL category_id rows
• Not handling NULL results from subquery