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

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

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

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

Need a hint?

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