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

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

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

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

Hint

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

Practice

1. Which of the following operators in PostgreSQL means "not equal to"?

easy

A. <=

B. =

C. <> or !=

D. >=

Solution

1. Step 1: Understand the meaning of operators

   The operator '=' means equal, so it cannot mean not equal.

2. Step 2: Identify the not equal operator

   In PostgreSQL, both '<>' and '!=' are used to mean not equal.

3. Final Answer:

   <> or != -> Option C

4. Quick Check:

   Not equal operator = <> or != [OK]

Hint: Remember: <> or != means not equal in SQL [OK]

Common Mistakes:

• Confusing '=' with not equal
• Using '<' or '>' as not equal
• Thinking '!=' is invalid in PostgreSQL

2. Which of the following is the correct syntax to check if column age is greater than or equal to 18 in PostgreSQL?

easy

A. age >> 18

B. age >= 18

C. age =< 18

D. age => 18

Solution

1. Step 1: Recall the correct operator for greater than or equal

   The correct operator is '>=' which means greater than or equal.

2. Step 2: Check each option's syntax

   age >= 18 uses '>=', which is correct. Options B and C use invalid operators '=>' and '=<' which are not valid in PostgreSQL. age >> 18 uses '>>' which is a bitwise operator, not comparison.

3. Final Answer:

   age >= 18 -> Option B

4. Quick Check:

   Greater or equal operator = >= [OK]

Hint: Use >= for greater or equal, not => or =< [OK]

Common Mistakes:

• Swapping operator symbols like => instead of >=
• Using bitwise operators instead of comparison
• Confusing <= and >= syntax

3. Given a table products with a column price, what will the following query return?

SELECT * FROM products WHERE price < 50;

medium

A. All products with price less than 50

B. All products with price greater than 50

C. All products with price equal to 50

D. Syntax error in query

Solution

1. Step 1: Understand the comparison operator used

   The operator '<' means less than, so the query filters rows where price is less than 50.

2. Step 2: Interpret the query result

   The query returns all rows from products where price is strictly less than 50.

3. Final Answer:

   All products with price less than 50 -> Option A

4. Quick Check:

   Operator '<' means less than [OK]

Hint: Remember: < means less than in SQL [OK]

Common Mistakes:

• Thinking '<' means less than or equal
• Confusing '<' with '>'
• Assuming syntax error due to operator

4. Identify the error in the following PostgreSQL query:

SELECT * FROM users WHERE age <> 30 AND age != 25;

medium

A. The comparison operators should be = instead of <> and !=

B. Using both <> and != together causes syntax error

C. The AND operator should be OR

D. No error; query is valid and works correctly

Solution

1. Step 1: Check the use of comparison operators

   Both '<>' and '!=' are valid not equal operators in PostgreSQL and can be used interchangeably.

2. Step 2: Analyze the logical operator usage

   The AND operator correctly combines two conditions requiring age to be not 30 and not 25 simultaneously.

3. Final Answer:

   No error; query is valid and works correctly -> Option D

4. Quick Check:

   Both <> and != are valid not equal operators [OK]

Hint: Both <> and != work for not equal in PostgreSQL [OK]

Common Mistakes:

• Assuming <> and != cannot be mixed
• Thinking AND should be OR without reason
• Confusing not equal with equal operator

5. You want to select all employees from a table employees whose salary is between 3000 and 7000 inclusive. Which of the following WHERE clauses correctly uses standard comparison operators to achieve this?

hard

A. salary >= 3000 AND salary <= 7000

B. salary > 3000 AND salary < 7000

C. salary = 3000 OR salary = 7000

D. salary <> 3000 AND salary <> 7000

Solution

1. Step 1: Understand the requirement for inclusive range

   Inclusive means salary can be equal to 3000 or 7000 or any value in between.

2. Step 2: Analyze each option's logic

   salary >= 3000 AND salary <= 7000 uses '>=' and '<=' which include the boundary values. salary > 3000 AND salary < 7000 excludes boundaries with '>' and '<'. salary = 3000 OR salary = 7000 only selects salaries exactly 3000 or 7000, missing values in between. salary <> 3000 AND salary <> 7000 excludes salaries equal to 3000 and 7000, opposite of requirement.

3. Final Answer:

   salary >= 3000 AND salary <= 7000 -> Option A

4. Quick Check:

   Use >= and <= for inclusive range [OK]

Hint: Use >= and <= for inclusive ranges in WHERE clause [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using OR instead of AND for range
• Using not equal operators instead of range