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

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

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

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

Hint

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

Practice

1. What is the main purpose of the WHERE clause in a PostgreSQL query?

easy

A. To create a new table

B. To sort the results alphabetically

C. To join two tables together

D. To filter rows based on a condition

Solution

1. Step 1: Understand the role of WHERE clause

   The WHERE clause is used to specify conditions that rows must meet to be included in the result.

2. Step 2: Compare options with WHERE purpose

   Sorting is done by ORDER BY, joining by JOIN, and creating tables by CREATE TABLE, so these are incorrect.

3. Final Answer:

   To filter rows based on a condition -> Option D

4. Quick Check:

   WHERE filters rows = A [OK]

Hint: WHERE filters rows to get only what you want [OK]

Common Mistakes:

• Confusing WHERE with ORDER BY
• Thinking WHERE creates tables
• Mixing WHERE with JOIN

2. Which of the following is the correct syntax to filter rows where the column age is greater than 30?

easy

A. SELECT * FROM users FILTER age > 30;

B. SELECT * FROM users WHERE age > 30;

C. SELECT * FROM users HAVING age > 30;

D. SELECT * FROM users ORDER BY age > 30;

Solution

1. Step 1: Identify correct filtering syntax

   The WHERE clause is used to filter rows based on conditions like age > 30.

2. Step 2: Eliminate incorrect clauses

   FILTER is not valid here, HAVING is for groups, ORDER BY sorts results, so only WHERE is correct.

3. Final Answer:

   SELECT * FROM users WHERE age > 30; -> Option B

4. Quick Check:

   WHERE filters rows correctly = A [OK]

Hint: Use WHERE for row conditions, HAVING for groups [OK]

Common Mistakes:

• Using HAVING without GROUP BY
• Using FILTER instead of WHERE
• Confusing ORDER BY with filtering

3. Given the table employees with columns id, name, and salary, what will this query return?

SELECT name FROM employees WHERE salary >= 50000;

medium

A. All employee names with salary equal or greater than 50000

B. Syntax error due to missing GROUP BY

C. All employee names regardless of salary

D. All employee names with salary less than 50000

Solution

1. Step 1: Analyze the WHERE condition

   The condition salary >= 50000 filters employees with salary 50000 or more.

2. Step 2: Understand SELECT output

   Only the name column is selected for those filtered employees.

3. Final Answer:

   All employee names with salary equal or greater than 50000 -> Option A

4. Quick Check:

   WHERE salary >= 50000 filters names = D [OK]

Hint: WHERE filters rows; SELECT chooses columns [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing >= with <
• Expecting GROUP BY needed here

4. Identify the error in this query:

SELECT * FROM orders WHERE status = 'shipped' AND;

medium

A. Incorrect use of WHERE instead of HAVING

B. Missing ORDER BY clause

C. Missing condition after AND

D. Using * instead of column names

Solution

1. Step 1: Check WHERE clause syntax

   The WHERE clause ends with AND but no condition follows, causing syntax error.

2. Step 2: Validate other parts

   HAVING is not needed here, ORDER BY is optional, and * is valid to select all columns.

3. Final Answer:

   Missing condition after AND -> Option C

4. Quick Check:

   AND must be followed by condition = B [OK]

Hint: AND must always have a condition after it [OK]

Common Mistakes:

• Leaving AND without condition
• Confusing WHERE and HAVING
• Thinking ORDER BY is mandatory

5. You want to find customers who placed orders with total amount over 100 but only if their account is active. Which query correctly filters this?

SELECT customer_id FROM orders WHERE total > 100 AND account_status = 'active';

hard

A. This query correctly filters customers with total > 100 and active accounts

B. The query should use HAVING instead of WHERE for filtering

C. The query needs GROUP BY customer_id to filter correctly

D. Filtering on account_status is invalid in WHERE clause

Solution

1. Step 1: Understand filtering conditions

   Both conditions total > 100 and account_status = 'active' filter rows correctly in WHERE clause.

2. Step 2: Check if GROUP BY or HAVING needed

   Since no aggregation is used, GROUP BY or HAVING is not required here.

3. Final Answer:

   This query correctly filters customers with total > 100 and active accounts -> Option A

4. Quick Check:

   WHERE filters multiple conditions correctly = C [OK]

Hint: Use WHERE for multiple conditions without aggregation [OK]

Common Mistakes:

• Using HAVING without aggregation
• Adding unnecessary GROUP BY
• Thinking account_status can't be filtered in WHERE