ALL, ANY, SOME with subqueries in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ALL with subquery

Given a table products with columns id, price, and category, what is the output of this query?

SELECT id FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

PostgreSQL

SELECT id FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

A Returns IDs of products priced higher than at least one product in the 'Books' category

B Returns IDs of products priced lower than every product in the 'Books' category

C Returns IDs of products priced higher than every product in the 'Books' category

D Returns IDs of products priced equal to the highest price in the 'Books' category

💡 Hint

ALL means the condition must be true for every value returned by the subquery.

The query selects products whose price is greater than every price of products in the 'Books' category. This means the product's price is higher than the maximum price in 'Books'.

❓ query_result

intermediate

Output of ANY with subquery

Consider a table employees with columns id, salary, and department. What does this query return?

SELECT id FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE department = 'Sales');

PostgreSQL

SELECT id FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE department = 'Sales');

A Employees with salary less than all employees in Sales

B Employees with salary less than at least one employee in Sales

C Employees with salary equal to the lowest salary in Sales

D Employees with salary greater than all employees in Sales

💡 Hint

ANY means the condition must be true for at least one value from the subquery.

The query returns employees whose salary is less than at least one salary in the Sales department. This includes employees with salary less than the maximum or any salary in Sales.

📝 Syntax

advanced

Identify the syntax error in this ALL query

Which option contains a syntax error when using ALL with a subquery in PostgreSQL?

PostgreSQL

SELECT id FROM orders WHERE amount > ALL SELECT amount FROM orders WHERE status = 'completed';

A Missing parentheses around the subquery after ALL

B Using ALL instead of ANY causes syntax error

C Subquery must use DISTINCT keyword with ALL

D ALL cannot be used with numeric columns

💡 Hint

Check if the subquery is properly enclosed.

The ALL operator requires the subquery to be enclosed in parentheses. Missing parentheses causes a syntax error.

❓ optimization

advanced

Optimizing ANY with subquery for performance

You have a large sales table with columns id, region, and amount. Which query is more efficient to find sales with amount less than any sale in region 'East'?

A SELECT id FROM sales WHERE amount < (SELECT MAX(amount) FROM sales WHERE region = 'East');

B SELECT id FROM sales WHERE EXISTS (SELECT 1 FROM sales s2 WHERE s2.region = 'East' AND amount < s2.amount);

C SELECT id FROM sales WHERE amount IN (SELECT amount FROM sales WHERE region = 'East');

D SELECT id FROM sales WHERE amount < ANY (SELECT amount FROM sales WHERE region = 'East');

💡 Hint

Rewrite ANY with an aggregate function to reduce subquery size.

Using MAX() in the subquery reduces the number of comparisons and improves performance compared to ANY with a subquery returning many rows.

🧠 Conceptual

expert

Understanding SOME synonym behavior

In PostgreSQL, which statement about the SOME keyword is correct?

A SOME requires the subquery to return only one row

B SOME returns true only if all values satisfy the condition

C SOME cannot be used with NOT operator

D SOME is a synonym for ANY and behaves identically in queries

💡 Hint

Check PostgreSQL documentation for SOME keyword meaning.

In PostgreSQL, SOME is exactly the same as ANY and can be used interchangeably.