Scalar subquery in SELECT in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the employee name and their department name using a scalar subquery.

SQL

SELECT employee_name, (SELECT [1] FROM departments WHERE departments.id = employees.department_id) AS department_name FROM employees;

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

Aemployee_name

Bid

Cname

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the department id instead of the name.

Using a column from the employees table inside the subquery.

The scalar subquery must select the name of the department to show it alongside the employee name.

2fill in blank

medium

Complete the code to find the total number of orders for each customer using a scalar subquery.

SQL

SELECT customer_name, (SELECT [1] FROM orders WHERE orders.customer_id = customers.id) AS total_orders FROM customers;

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

Acustomer_id

BCOUNT(*)

Corders

Dcustomer_name

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting a column name instead of counting rows.

Using the wrong column in the WHERE clause.

The scalar subquery must count the number of orders, so COUNT(*) is used.

3fill in blank

hard

Fix the error in the scalar subquery to get the highest salary from the employees table.

SQL

SELECT employee_name, (SELECT MAX([1]) FROM employees) AS highest_salary FROM employees;

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

Asalary

Bid

Cdepartment_id

Demployee_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-numeric column inside MAX().

Selecting employee_name inside the subquery.

The MAX function should be applied to the salary column to find the highest salary.

4fill in blank

hard

Fill both blanks to select each product's name and its category name using a scalar subquery.

SQL

SELECT product_name, (SELECT [1] FROM categories WHERE categories.id = products.[2]) AS category_name FROM products;

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

Aname

Bcategory_id

Cid

Dproduct_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using product_name instead of category_id in the WHERE clause.

Selecting the wrong column from categories.

The subquery selects the name from categories where the category's id matches the product's category_id.

5fill in blank

hard

Fill all three blanks to select each student's name, their highest test score, and the test date using scalar subqueries.

SQL

SELECT student_name, (SELECT MAX([1]) FROM test_scores WHERE test_scores.student_id = students.[2]) AS highest_score, (SELECT [3] FROM test_scores WHERE test_scores.student_id = students.id ORDER BY score DESC LIMIT 1) AS test_date FROM students;

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

Ascore

Bid

Ctest_date

Dstudent_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using student_name instead of id in the WHERE clause.

Selecting score instead of test_date for the date.

Not ordering the subquery to get the highest score's date.

The first subquery finds the maximum score for each student matching by id. The second subquery selects the test_date of the highest score.

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