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

Need a 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

Need a 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

Need a 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

Need a hint?

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