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.