Scalar subqueries in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of scalar subquery in SELECT clause

What is the output of this query?

SELECT name, (SELECT MAX(score) FROM results WHERE results.user_id = users.id) AS max_score FROM users ORDER BY id;

PostgreSQL

CREATE TABLE users (id INT, name TEXT);
CREATE TABLE results (user_id INT, score INT);

INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO results VALUES (1, 50), (1, 70), (2, 40);

A [{"name": "Alice", "max_score": 50}, {"name": "Bob", "max_score": 40}]

B [{"name": "Alice", "max_score": 70}, {"name": "Bob", "max_score": null}]

C [{"name": "Alice", "max_score": 70}, {"name": "Bob", "max_score": 40}]

D [{"name": "Alice", "max_score": null}, {"name": "Bob", "max_score": 40}]

💡 Hint

Think about how the subquery filters results by user_id and finds the maximum score for each user.

The scalar subquery finds the maximum score for each user by matching user_id. Alice has scores 50 and 70, max is 70. Bob has one score 40, max is 40.

📝 Syntax

intermediate

Identify the syntax error in scalar subquery

Which option contains a syntax error in the scalar subquery usage?

PostgreSQL

SELECT name, (SELECT score FROM results WHERE user_id = users.id ORDER BY score DESC LIMIT 1) AS top_score FROM users;

A SELECT name, (SELECT score FROM results WHERE user_id = users.id ORDER BY score DESC LIMIT 1) AS top_score FROM users;

B SELECT name, (SELECT score FROM results WHERE user_id = users.id LIMIT 1 ORDER BY score DESC) AS top_score FROM users;

C SELECT name, (SELECT score FROM results WHERE user_id = users.id ORDER BY score DESC LIMIT 1) top_score FROM users;

D ;sresu MORF erocs_pot SA )1 TIMIL CSED erocs YB REDRO di.sresu = di_resu EREHW stluser MORF erocs TCELES( ,eman TCELES

💡 Hint

Check the order of clauses in the subquery.

In SQL, LIMIT must come after ORDER BY. Option B places LIMIT before ORDER BY, causing a syntax error.

❓ optimization

advanced

Optimizing scalar subquery for performance

Which query is more efficient to get each user's highest score?

PostgreSQL

Tables: users(id, name), results(user_id, score)

A SELECT name, (SELECT MAX(score) FROM results WHERE user_id = users.id) AS max_score FROM users;

B SELECT name, (SELECT score FROM results WHERE user_id = users.id ORDER BY score DESC LIMIT 1) AS max_score FROM users;

C SELECT name, MAX(score) FROM results GROUP BY user_id;

D SELECT users.name, MAX(results.score) FROM users JOIN results ON users.id = results.user_id GROUP BY users.name;

💡 Hint

Consider how JOIN and GROUP BY can reduce repeated subquery executions.

Option D uses a JOIN and GROUP BY to compute max scores in one pass, avoiding repeated scalar subqueries per user.

🧠 Conceptual

advanced

Understanding scalar subquery behavior with NULL results

What will be the output of this query if a user has no matching rows in results?

SELECT name, (SELECT MAX(score) FROM results WHERE user_id = users.id) AS max_score FROM users;

PostgreSQL

INSERT INTO users VALUES (3, 'Charlie'); -- Charlie has no results

A [{"name": "Charlie", "max_score": null}]

B [{"name": "Charlie", "max_score": 0}]

C Query raises an error due to no matching rows

D [{"name": "Charlie", "max_score": ""}]

💡 Hint

Think about what MAX returns when no rows match.

MAX returns NULL when no rows match the condition, so max_score is NULL for Charlie.

🔧 Debug

expert

Debugging unexpected scalar subquery result

Why does this query return the same max_score for all users?

SELECT name, (SELECT MAX(score) FROM results) AS max_score FROM users;

PostgreSQL

Tables: users(id, name), results(user_id, score)

A The subquery lacks correlation to users, so it returns the global max score for all rows.

B The subquery is missing a GROUP BY clause causing aggregation error.

C The subquery is invalid because MAX cannot be used without WHERE clause.

D The query is missing a JOIN between users and results causing NULL results.

💡 Hint

Check if the subquery depends on the outer query row.

The scalar subquery is uncorrelated; it returns the same max score for all users because it does not filter by user_id.

Practice

1. What does a scalar subquery return in PostgreSQL?

easy

A. A single value used inside another query

B. Multiple rows and columns

C. Only table names

D. A list of table columns

Solution

1. Step 1: Understand scalar subquery definition

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

2. Step 2: Compare options with definition

   Only A single value used inside another query describes a single value used inside another query, matching the scalar subquery concept.

3. Final Answer:

   A single value used inside another query -> Option A

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 names
• Assuming scalar subqueries return lists

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

easy

A. SELECT (SELECT MAX(price) FROM products) AS max_price;

B. SELECT MAX(price) FROM products WHERE price = (SELECT);

C. SELECT MAX(price) FROM products SELECT price;

D. SELECT MAX(price) FROM (SELECT price FROM products);

Solution

1. Step 1: Identify correct scalar subquery syntax

   The scalar subquery must be enclosed in parentheses and return a single value, used inside the main SELECT.

2. Step 2: Check each option

   SELECT (SELECT MAX(price) FROM products) AS max_price; correctly uses (SELECT MAX(price) FROM products) as a scalar subquery. Options A, B, and D have syntax errors or misuse subqueries.

3. Final Answer:

   SELECT (SELECT MAX(price) FROM products) AS max_price; -> Option A

4. Quick Check:

   Scalar subquery syntax = SELECT (SELECT MAX(price) FROM products) AS max_price; [OK]

Hint: Scalar subqueries are enclosed in parentheses inside SELECT [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Using incomplete or invalid subquery syntax
• Confusing subqueries with JOINs

3. Given the tables:
products(id, name, price)
and
sales(product_id, quantity)
What will this query return?

SELECT name, (SELECT SUM(quantity) FROM sales WHERE product_id = products.id) AS total_sold FROM products;

medium

A. Syntax error due to subquery

B. Only product names without sales data

C. Sum of all sales quantities for all products

D. Each product's name with total quantity sold

Solution

1. Step 1: Understand the subquery role

   The subquery sums quantity from sales where product_id matches the current product's id, returning total sold per product.

2. Step 2: Analyze the main query output

   The main query selects product name and the scalar subquery result as total_sold for each product row.

3. Final Answer:

   Each product's name with total quantity sold -> Option D

4. Quick Check:

   Scalar subquery sums sales per product = Each product's name with total quantity sold [OK]

Hint: Scalar subquery calculates per-row related value [OK]

Common Mistakes:

• Thinking subquery sums all sales once
• Ignoring WHERE clause linking tables
• Assuming syntax error without checking carefully

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

SELECT name, (SELECT price FROM products) AS product_price FROM products;

medium

A. Missing alias for subquery

B. Incorrect table name in subquery

C. Subquery returns multiple rows causing error

D. No error, query runs fine

Solution

1. Step 1: Check subquery result cardinality

   The subquery SELECT price FROM products returns multiple prices (rows), not a single value.

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 error -> Option C

4. Quick Check:

   Scalar subquery must return one value [OK]

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

Common Mistakes:

• Ignoring multiple rows returned by subquery
• Assuming alias fixes multiple rows error
• Confusing syntax error with runtime error

5. You want to list each product's name and its price difference from the average price of all products. Which query correctly uses a scalar subquery to achieve this?

hard

A. SELECT name, price - AVG(price) FROM products;

B. SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products;

C. SELECT name, price - (SELECT price FROM products) AS price_diff FROM products;

D. SELECT name, price - (SELECT SUM(price) FROM products) AS price_diff FROM products;

Solution

1. Step 1: Understand the goal

   We want each product's price minus the average price of all products.

2. Step 2: Analyze each option

   SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; correctly uses a scalar subquery to get AVG(price) once and subtracts it from each product's price. SELECT name, price - AVG(price) FROM products; lacks GROUP BY and will error. SELECT name, price - (SELECT price FROM products) AS price_diff FROM products; returns multiple prices, causing error. SELECT name, price - (SELECT SUM(price) FROM products) AS price_diff FROM products; subtracts sum, not average, which is incorrect.

3. Final Answer:

   SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; -> Option B

4. Quick Check:

   Scalar subquery for AVG(price) = SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; [OK]

Hint: Use scalar subquery for single aggregate value [OK]

Common Mistakes:

• Using aggregate without GROUP BY causing error
• Subquery returning multiple rows instead of one
• Confusing SUM with AVG in calculation