Standard comparison operators in PostgreSQL - Mini Project: Build & Apply

Using Standard Comparison Operators in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to find books based on their prices using comparison operators.

🎯 Goal: Build SQL queries step-by-step that use standard comparison operators to filter books by price.

📋 What You'll Learn

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

Insert specific book records with exact prices

Write a query using the < operator to find books cheaper than a certain price

Write a query using the = operator to find books with an exact price

Write a query using the >= operator to find books priced at or above a certain value

💡 Why This Matters

🌍 Real World

Filtering products or items by price or other numeric attributes is common in online stores, inventory systems, and reporting tools.

💼 Career

Understanding comparison operators is essential for writing effective SQL queries to retrieve specific data from databases in many job roles like data analyst, backend developer, and database administrator.

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 exact rows: (1, 'The Hobbit', 15.99), (2, '1984', 12.50), (3, 'Dune', 18.00), (4, 'Fahrenheit 451', 12.50).

PostgreSQL

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

Need a hint?

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

2

Set a price threshold variable

Create a variable called price_limit and set it to 13.00 to use as a price threshold for filtering books.

PostgreSQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'The Hobbit', 15.99),
(2, '1984', 12.50),
(3, 'Dune', 18.00),
(4, 'Fahrenheit 451', 12.50);

-- Set the price_limit variable
-- Your code here

Need a hint?

Use \set in psql to define a variable for reuse in queries.

3

Query books cheaper than the price limit

Write a SQL query to select all columns from books where price is less than the variable :price_limit using the < operator.

PostgreSQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'The Hobbit', 15.99),
(2, '1984', 12.50),
(3, 'Dune', 18.00),
(4, 'Fahrenheit 451', 12.50);

\set price_limit 13.00

-- Select books where price is less than :price_limit
-- Your code here

Need a hint?

Use WHERE price < :price_limit to filter books cheaper than the threshold.

4

Query books with exact and minimum prices

Write two SQL queries: one to select all columns from books where price equals 12.50 using =, and another to select all columns where price is greater than or equal to 15.99 using >=.

PostgreSQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'The Hobbit', 15.99),
(2, '1984', 12.50),
(3, 'Dune', 18.00),
(4, 'Fahrenheit 451', 12.50);

\set price_limit 13.00

SELECT * FROM books WHERE price = 15.99
-- Your code here

Need a hint?

Use = for exact match and >= for minimum price filtering.