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.