ALL, ANY, SOME with subqueries in PostgreSQL - Mini Project: Build & Apply

Using ALL, ANY, and SOME with Subqueries in PostgreSQL

📖 Scenario: You are managing a small online bookstore database. You want to find books based on their prices compared to other books in the store.

🎯 Goal: Build SQL queries using ALL, ANY, and SOME with subqueries to compare book prices.

📋 What You'll Learn

Create a table called books with columns id, title, and price.

Insert exactly five books with specified titles and prices.

Write a query using ALL to find books priced higher than all books priced below 20.

Write a query using ANY to find books priced lower than any book priced above 25.

Write a query using SOME to find books priced equal to some book priced exactly 15.

💡 Why This Matters

🌍 Real World

Comparing product prices or attributes in an online store to filter items based on relative values.

💼 Career

Database developers and analysts often use ALL, ANY, and SOME with subqueries to write flexible and powerful queries for business insights.

1

Create the books table and insert data

Create a table called books with columns id as integer primary key, title as text, and price as numeric. Then insert these five books exactly: (1, 'Book A', 10), (2, 'Book B', 15), (3, 'Book C', 20), (4, 'Book D', 25), (5, 'Book E', 30).

PostgreSQL

-- Create the books table and insert the five books
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Set up a subquery for prices below 20

Create a subquery named sub_prices_below_20 that selects the price from books where the price is less than 20.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 15),
  (3, 'Book C', 20),
  (4, 'Book D', 25),
  (5, 'Book E', 30);

-- Create a subquery named sub_prices_below_20 selecting price from books where price < 20
-- Your code here

Hint

Use a WITH clause to create the subquery.

3

Write a query using ALL to find books priced higher than all prices below 20

Using the sub_prices_below_20 subquery, write a query to select title and price from books where the book's price is greater than ALL prices in sub_prices_below_20.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 15),
  (3, 'Book C', 20),
  (4, 'Book D', 25),
  (5, 'Book E', 30);

WITH sub_prices_below_20 AS (
  SELECT price FROM books WHERE price  ALL prices in sub_prices_below_20
-- Your code here

Hint

Use price > ALL (SELECT price FROM sub_prices_below_20) in the WHERE clause.

4

Write queries using ANY and SOME with subqueries

Write two queries: (1) select title and price from books where price is less than ANY price from books priced above 25; (2) select title and price from books where price equals SOME price from books priced exactly 15.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 15),
  (3, 'Book C', 20),
  (4, 'Book D', 25),
  (5, 'Book E', 30);

WITH sub_prices_below_20 AS (
  SELECT price FROM books WHERE price  ALL (SELECT price FROM sub_prices_below_20);

-- Query 1: Select title and price where price < ANY price from books priced above 25
-- Query 2: Select title and price where price = SOME price from books priced exactly 15
-- Your code here

Hint

Use price < ANY (SELECT price FROM books WHERE price > 25) and price = SOME (SELECT price FROM books WHERE price = 15).

Practice

1. What does the ALL keyword do in a PostgreSQL query with a subquery?

easy

A. Returns all rows from the main query regardless of the subquery.

B. Checks if a condition is true for at least one value returned by the subquery.

C. Checks if a condition is true for every value returned by the subquery.

D. Joins the main query with the subquery on matching columns.

Solution

1. Step 1: Understand the role of ALL

   The ALL keyword compares a value to every value returned by a subquery.

2. Step 2: Interpret the condition

   It returns true only if the condition holds for all values from the subquery.

3. Final Answer:

   Checks if a condition is true for every value returned by the subquery. -> Option C

4. Quick Check:

   ALL means every value must satisfy condition [OK]

Hint: ALL means every value must meet the condition [OK]

Common Mistakes:

• Confusing ALL with ANY or SOME
• Thinking ALL returns all rows without condition
• Assuming ALL works like a JOIN

2. Which of the following is the correct syntax to use ANY with a subquery in PostgreSQL?

easy

A. SELECT * FROM table WHERE column = ANY (SELECT col FROM othertable);

B. SELECT * FROM table WHERE column ANY = (SELECT col FROM othertable);

C. SELECT * FROM table WHERE ANY column = (SELECT col FROM othertable);

D. SELECT * FROM table WHERE column = (ANY SELECT col FROM othertable);

Solution

1. Step 1: Recall correct ANY syntax

   ANY is used as: column operator ANY (subquery), e.g., column = ANY (subquery).

2. Step 2: Check each option

   SELECT * FROM table WHERE column = ANY (SELECT col FROM othertable); matches correct syntax. Others have misplaced ANY or wrong order.

3. Final Answer:

   SELECT * FROM table WHERE column = ANY (SELECT col FROM othertable); -> Option A

4. Quick Check:

   ANY goes after operator and before subquery in parentheses [OK]

Hint: Use operator = ANY (subquery) syntax [OK]

Common Mistakes:

• Placing ANY before the column name
• Omitting parentheses around subquery
• Using ANY without an operator

3. Given the tables:
products(id, price)
and
discounts(min_price)
What does this query return?

SELECT id FROM products WHERE price > ALL (SELECT min_price FROM discounts);

medium

A. All product ids regardless of price.

B. Product ids with price greater than every min_price in discounts.

C. Product ids with price equal to any min_price in discounts.

D. Product ids with price greater than at least one min_price in discounts.

Solution

1. Step 1: Understand the ALL condition

   The condition price > ALL (subquery) means price must be greater than every min_price returned.

2. Step 2: Interpret the query result

   Only products with price higher than all discount min_prices are selected.

3. Final Answer:

   Product ids with price greater than every min_price in discounts. -> Option B

4. Quick Check:

   price > ALL means greater than all values [OK]

Hint: ALL means condition holds for every subquery value [OK]

Common Mistakes:

• Confusing ALL with ANY or SOME
• Thinking it returns products with price greater than some min_price
• Assuming it returns all products

4. Identify the error in this query:

SELECT name FROM employees WHERE salary > ANY SELECT bonus FROM bonuses;

medium

A. Using ANY instead of ALL causes syntax error.

B. Incorrect use of > operator with ANY.

C. Subquery should be in FROM clause, not WHERE.

D. Missing parentheses around the subquery after ANY.

Solution

1. Step 1: Check ANY syntax

   ANY must be followed by a subquery enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query lacks parentheses around SELECT bonus FROM bonuses after ANY.

3. Final Answer:

   Missing parentheses around the subquery after ANY. -> Option D

4. Quick Check:

   ANY requires parentheses around subquery [OK]

Hint: Always put subquery in parentheses after ANY [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Misplacing ANY keyword
• Confusing ANY with ALL syntax

5. You want to find all customers whose order total is less than the maximum order total of any customer in the 'orders' table. Which query correctly uses ANY to achieve this?

hard

A. SELECT customer_id FROM orders WHERE total < ANY (SELECT total FROM orders);

B. SELECT customer_id FROM orders WHERE total < ALL (SELECT total FROM orders);

C. SELECT customer_id FROM orders WHERE total > ANY (SELECT total FROM orders);

D. SELECT customer_id FROM orders WHERE total = ANY (SELECT MAX(total) FROM orders);

Solution

1. Step 1: Understand the goal

   We want customers with order total less than the maximum order total of any customer.

2. Step 2: Analyze options

   SELECT customer_id FROM orders WHERE total < ANY (SELECT total FROM orders); checks if total is less than any total in orders, which includes the maximum, so it works.

3. Step 3: Why others are wrong

   SELECT customer_id FROM orders WHERE total < ALL (SELECT total FROM orders); uses ALL, meaning less than every total, which is too strict. SELECT customer_id FROM orders WHERE total > ANY (SELECT total FROM orders); checks greater than any total, wrong direction. SELECT customer_id FROM orders WHERE total = ANY (SELECT MAX(total) FROM orders); compares to MAX(total) with ANY, which is redundant and incorrect.

4. Final Answer:

   SELECT customer_id FROM orders WHERE total < ANY (SELECT total FROM orders); -> Option A

5. Quick Check:

   Use ANY to compare with multiple values including max [OK]

Hint: Use ANY to compare with multiple values including max [OK]

Common Mistakes:

• Using ALL instead of ANY for less than max
• Comparing with MAX inside ANY incorrectly
• Confusing > and < operators