ANY and ALL with arrays in PostgreSQL - Mini Project: Build & Apply

Using ANY and ALL with Arrays in PostgreSQL

📖 Scenario: You work at a bookstore that keeps track of book prices in different stores using arrays. You want to find books based on price comparisons using ANY and ALL with arrays.

🎯 Goal: Build SQL queries that use ANY and ALL operators with arrays to filter books by price conditions.

📋 What You'll Learn

Create a table called books with columns id (integer), title (text), and prices (integer array).

Insert three books with exact titles and prices arrays as specified.

Write a query to find books where any price is less than 20 using ANY.

Write a query to find books where all prices are greater than 15 using ALL.

💡 Why This Matters

🌍 Real World

Stores often keep multiple prices or ratings for products in arrays. Using ANY and ALL helps filter products based on these multiple values easily.

💼 Career

Database developers and analysts use ANY and ALL with arrays to write efficient queries that handle multi-valued columns, common in real-world databases.

1

Create the books table and insert data

Create a table called books with columns id as integer, title as text, and prices as integer array. Then insert these three rows exactly: (1, 'Learn SQL', ARRAY[10, 15, 20]), (2, 'Mastering PostgreSQL', ARRAY[25, 30, 35]), and (3, 'Database Basics', ARRAY[5, 10, 15]).

PostgreSQL

-- Create the books table and insert the three rows
-- Your code here

Hint

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

2

Set a price threshold variable

Create a variable called price_limit and set it to 20. This will be used to compare prices in the next queries.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  prices INTEGER[]
);

INSERT INTO books (id, title, prices) VALUES
  (1, 'Learn SQL', ARRAY[10, 15, 20]),
  (2, 'Mastering PostgreSQL', ARRAY[25, 30, 35]),
  (3, 'Database Basics', ARRAY[5, 10, 15]);

-- Set the price_limit variable to 20
-- Your code here

Hint

Use the psql command \set to define a variable for the price limit.

3

Query books where any price is less than price_limit

Write a SQL query to select id and title from books where any price in the prices array is less than the variable :price_limit using the ANY operator.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  prices INTEGER[]
);

INSERT INTO books (id, title, prices) VALUES
  (1, 'Learn SQL', ARRAY[10, 15, 20]),
  (2, 'Mastering PostgreSQL', ARRAY[25, 30, 35]),
  (3, 'Database Basics', ARRAY[5, 10, 15]);

\set price_limit 20

-- Select id and title where any price is less than :price_limit using ANY
-- Your code here

Hint

Use WHERE prices < ANY (ARRAY[:price_limit]) to check if any price is less than the limit.

4

Query books where all prices are greater than 15 using ALL

Write a SQL query to select id and title from books where all prices in the prices array are greater than 15 using the ALL operator.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  prices INTEGER[]
);

INSERT INTO books (id, title, prices) VALUES
  (1, 'Learn SQL', ARRAY[10, 15, 20]),
  (2, 'Mastering PostgreSQL', ARRAY[25, 30, 35]),
  (3, 'Database Basics', ARRAY[5, 10, 15]);

\set price_limit 20

SELECT id, title
FROM books
WHERE prices < ANY (ARRAY[:price_limit]);

-- Select id and title where all prices are greater than 15 using ALL
-- Your code here

Hint

Use WHERE prices > ALL (ARRAY[15]) to check if all prices are greater than 15.

Practice

1. What does the ANY keyword do when used with an array in PostgreSQL?

easy

A. It checks if a value matches at least one element in the array.

B. It checks if a value matches all elements in the array.

C. It returns all elements of the array.

D. It returns the first element of the array.

Solution

1. Step 1: Understand the ANY keyword

   ANY compares a single value to each element in an array and returns true if it matches any one of them.

2. Step 2: Compare with ALL keyword

   ALL requires the value to match every element, which is different from ANY.

3. Final Answer:

   It checks if a value matches at least one element in the array. -> Option A

4. Quick Check:

   ANY = matches any element [OK]

Hint: ANY means match at least one element in array [OK]

Common Mistakes:

• Confusing ANY with ALL
• Thinking ANY returns all elements
• Assuming ANY returns first element only

2. Which of the following is the correct syntax to check if the number 5 is equal to any element in the array ARRAY[3,5,7] in PostgreSQL?

easy

A. SELECT 5 = ANY ARRAY[3,5,7];

B. SELECT 5 = ANY[3,5,7];

C. SELECT 5 = ANY(ARRAY[3,5,7]);

D. SELECT 5 = ALL ARRAY(3,5,7);

Solution

1. Step 1: Check correct ANY syntax

   The correct syntax uses = ANY(ARRAY[...]) with parentheses around ARRAY.

2. Step 2: Identify wrong options

   SELECT 5 = ANY ARRAY[3,5,7]; misses parentheses around ARRAY, causing syntax error. SELECT 5 = ANY[3,5,7]; uses square brackets incorrectly. SELECT 5 = ALL ARRAY(3,5,7); uses ALL instead of ANY and wrong ARRAY syntax without [].

3. Final Answer:

   SELECT 5 = ANY(ARRAY[3,5,7]); -> Option C

4. Quick Check:

   = ANY(ARRAY[...]) [OK]

Hint: Use = ANY(ARRAY[...]) with parentheses [OK]

Common Mistakes:

• Omitting parentheses around ARRAY
• Using square brackets instead of ARRAY keyword
• Mixing ANY and ALL keywords

3. Given the query:
SELECT 4 > ALL ARRAY[2,3,5];
What is the result?

medium

A. true

B. null

C. false

D. syntax error

Solution

1. Step 1: Understand ALL with comparison

   The query uses 4 > ALL ARRAY[2,3,5] but misses required parentheses around the array.

2. Step 2: Correct syntax and error

   Correct is 4 > ALL (ARRAY[2,3,5]). Without (), it causes syntax error. (Note: if fixed, 4>5 is false so result false, but as given: error.)

3. Final Answer:

   syntax error -> Option D

4. Quick Check:

   Missing () around ARRAY -> syntax error [OK]

Hint: op ALL requires (ARRAY[...]) [OK]

Common Mistakes:

• Assuming query runs and gets false
• Forgetting parentheses around ARRAY
• Confusing with ANY behavior

4. Identify the error in this query:
SELECT * FROM users WHERE age = ALL [20, 30, 40];

medium

A. ALL cannot be used with numbers

B. Using square brackets instead of ARRAY keyword

C. Missing comparison operator

D. No error, query is correct

Solution

1. Step 1: Check array syntax in WHERE clause

   PostgreSQL requires arrays to be written with ARRAY keyword, not square brackets alone.

2. Step 2: Identify correct syntax

   The correct syntax is age = ALL (ARRAY[20, 30, 40]). Using just [20, 30, 40] causes syntax error.

3. Final Answer:

   Using square brackets instead of ARRAY keyword -> Option B

4. Quick Check:

   Arrays need ARRAY keyword, not just brackets [OK]

Hint: Use ARRAY[...] not just brackets for arrays [OK]

Common Mistakes:

• Using [] instead of ARRAY[]
• Thinking ALL can't compare numbers
• Missing operator after WHERE

5. You want to find all products priced less than every price in the array ARRAY[100, 200, 300]. Which query correctly achieves this?

hard

A. SELECT * FROM products WHERE price < ALL (ARRAY[100, 200, 300]);

B. SELECT * FROM products WHERE price < ANY ARRAY[100, 200, 300];

C. SELECT * FROM products WHERE price > ALL ARRAY[100, 200, 300];

D. SELECT * FROM products WHERE price = ALL ARRAY[100, 200, 300];

Solution

1. Step 1: Understand the requirement

   We want products with price less than every price in the array, meaning price must be less than 100 (the minimum).

2. Step 2: Choose correct operator and keyword

   Using price < ALL (ARRAY[100, 200, 300]) checks if price is less than all elements. ANY would only require less than one.

3. Final Answer:

   SELECT * FROM products WHERE price < ALL (ARRAY[100, 200, 300]); -> Option A

4. Quick Check:

   Less than ALL means less than every element [OK]

Hint: Use < ALL (ARRAY[...]) to check less than every element [OK]

Common Mistakes:

• Using ANY instead of ALL for all elements
• Using > instead of < for less than
• Using = ALL which checks equality