Why filtering behavior matters in PostgreSQL - See It in Action

Why Filtering Behavior Matters

📖 Scenario: You are managing a small bookstore database. You want to find books that are currently in stock and priced below a certain amount. Filtering helps you get only the books you want to see, saving time and effort.

🎯 Goal: Build a simple query that filters books based on stock availability and price.

📋 What You'll Learn

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

Insert 5 specific book records with given values

Set a price limit variable

Write a SELECT query that filters books where in_stock is true and price is less than the price limit

💡 Why This Matters

🌍 Real World

Filtering data is essential in databases to find exactly what you need quickly, like finding affordable books in stock.

💼 Career

Database filtering skills are fundamental for 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 (integer), title (text), price (numeric), and in_stock (boolean). Then insert these exact rows: (1, 'Learn SQL', 25.00, true), (2, 'Python Basics', 30.00, false), (3, 'Data Science', 45.00, true), (4, 'Web Design', 20.00, true), (5, 'Machine Learning', 50.00, false).

PostgreSQL

-- Create the books table and insert the 5 rows below
-- Your code here

Need a hint?

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

2

Set the price limit variable

Create a variable called price_limit and set it to 30.00. This will be the maximum price for filtering books.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC,
  in_stock BOOLEAN
);

INSERT INTO books (id, title, price, in_stock) VALUES
  (1, 'Learn SQL', 25.00, true),
  (2, 'Python Basics', 30.00, false),
  (3, 'Data Science', 45.00, true),
  (4, 'Web Design', 20.00, true),
  (5, 'Machine Learning', 50.00, false);

-- Set the price_limit variable below
-- Your code here

Need a hint?

Use \set price_limit 30.00 to define a variable in psql.

3

Write the filtering query

Write a SELECT query to get id, title, and price from books where in_stock is true and price is less than the variable :price_limit.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC,
  in_stock BOOLEAN
);

INSERT INTO books (id, title, price, in_stock) VALUES
  (1, 'Learn SQL', 25.00, true),
  (2, 'Python Basics', 30.00, false),
  (3, 'Data Science', 45.00, true),
  (4, 'Web Design', 20.00, true),
  (5, 'Machine Learning', 50.00, false);

\set price_limit 30.00

-- Write the SELECT query with filtering below
-- Your code here

Need a hint?

Use WHERE to filter rows by in_stock and price.

4

Complete the query with ordering

Add an ORDER BY price ASC clause to the query to show the cheapest books first.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC,
  in_stock BOOLEAN
);

INSERT INTO books (id, title, price, in_stock) VALUES
  (1, 'Learn SQL', 25.00, true),
  (2, 'Python Basics', 30.00, false),
  (3, 'Data Science', 45.00, true),
  (4, 'Web Design', 20.00, true),
  (5, 'Machine Learning', 50.00, false);

\set price_limit 30.00

SELECT id, title, price
FROM books
WHERE in_stock = true AND price < :price_limit
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY price ASC to sort results by price from low to high.