Boolean column filtering patterns in PostgreSQL - Mini Project: Build & Apply

Boolean Column Filtering Patterns

📖 Scenario: You are managing a small online store database. You have a table called products that stores product details including whether each product is currently in_stock (a boolean column).You want to learn how to filter products based on this boolean column to find which products are available or not.

🎯 Goal: Build SQL queries step-by-step to filter products based on the in_stock boolean column using different filtering patterns.

📋 What You'll Learn

Create a products table with columns id (integer), name (text), and in_stock (boolean).

Insert specific product data with exact values.

Write a query to select only products that are in stock.

Write a query to select only products that are out of stock.

💡 Why This Matters

🌍 Real World

Filtering boolean columns is common in databases to find records that meet yes/no or true/false conditions, such as availability, status flags, or feature toggles.

💼 Career

Database developers and analysts often write boolean filters to quickly find relevant data, making this skill essential for querying and reporting.

1

Create the products table and insert data

Create a table called products with columns id as integer, name as text, and in_stock as boolean. Then insert these exact rows: (1, 'Laptop', true), (2, 'Mouse', false), (3, 'Keyboard', true).

PostgreSQL

-- Create the products table and insert the rows
-- Your code here

Hint

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

2

Set up a query to select all products

Write a SQL query that selects all columns from the products table without any filtering.

PostgreSQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  in_stock BOOLEAN
);

INSERT INTO products (id, name, in_stock) VALUES
  (1, 'Laptop', TRUE),
  (2, 'Mouse', FALSE),
  (3, 'Keyboard', TRUE);

-- Write a query to select all products
-- Your code here

Hint

Use SELECT * FROM products; to get all rows and columns.

3

Filter products that are in stock

Write a SQL query to select all columns from products where the in_stock column is true.

PostgreSQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  in_stock BOOLEAN
);

INSERT INTO products (id, name, in_stock) VALUES
  (1, 'Laptop', TRUE),
  (2, 'Mouse', FALSE),
  (3, 'Keyboard', TRUE);

-- Write a query to select products where in_stock is true
-- Your code here

Hint

Use WHERE in_stock = TRUE to filter only products that are available.

4

Filter products that are out of stock

Write a SQL query to select all columns from products where the in_stock column is false.

PostgreSQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  in_stock BOOLEAN
);

INSERT INTO products (id, name, in_stock) VALUES
  (1, 'Laptop', TRUE),
  (2, 'Mouse', FALSE),
  (3, 'Keyboard', TRUE);

-- Write a query to select products where in_stock is false
-- Your code here

Hint

Use WHERE in_stock = FALSE to filter only products that are not available.

Practice

1. Which SQL condition correctly filters rows where a boolean column is_active is true?

easy

A. WHERE NOT is_active

B. WHERE is_active = FALSE

C. WHERE is_active = NULL

D. WHERE is_active = TRUE

Solution

1. Step 1: Understand boolean filtering for true values

   To filter rows where a boolean column is true, you can use WHERE column = TRUE or simply WHERE column.

2. Step 2: Evaluate each option

   WHERE is_active = TRUE uses WHERE is_active = TRUE, which is correct. WHERE is_active = FALSE filters false values. WHERE NOT is_active filters false values by negation. WHERE is_active = NULL compares to NULL, which is invalid for boolean filtering.

3. Final Answer:

   WHERE is_active = TRUE -> Option D

4. Quick Check:

   Boolean true filter = WHERE column = TRUE [OK]

Hint: Use WHERE column = TRUE or just WHERE column for true [OK]

Common Mistakes:

• Using WHERE column = FALSE to find true rows
• Using WHERE NOT column to find true rows
• Comparing boolean to NULL

2. Which of the following is the correct syntax to filter rows where the boolean column is_verified is false in PostgreSQL?

easy

A. WHERE is_verified = TRUE

B. WHERE is_verified == FALSE

C. WHERE NOT is_verified

D. WHERE is_verified = NULL

Solution

1. Step 1: Recall boolean false filtering syntax

   To filter false boolean values, use WHERE column = FALSE or WHERE NOT column.

2. Step 2: Check each option's syntax

   WHERE NOT is_verified uses WHERE NOT is_verified, which is valid and filters false values. WHERE is_verified = TRUE filters true values. WHERE is_verified == FALSE uses double equals which is invalid in SQL. WHERE is_verified = NULL compares to NULL, which is incorrect.

3. Final Answer:

   WHERE NOT is_verified -> Option C

4. Quick Check:

   Boolean false filter = WHERE NOT column [OK]

Hint: Use WHERE NOT column or WHERE column = FALSE for false [OK]

Common Mistakes:

• Using == instead of = in SQL
• Comparing boolean to NULL
• Using WHERE column = TRUE to find false rows

3. Given the table users with a boolean column is_admin, what will the following query return?

SELECT username FROM users WHERE is_admin;

medium

A. All usernames where is_admin is true

B. All usernames where is_admin is false

C. All usernames regardless of is_admin value

D. Syntax error due to missing comparison

Solution

1. Step 1: Understand filtering by boolean column directly

   In PostgreSQL, using WHERE is_admin filters rows where is_admin is true.

2. Step 2: Analyze the query result

   The query selects usernames only where is_admin is true, so it returns those usernames.

3. Final Answer:

   All usernames where is_admin is true -> Option A

4. Quick Check:

   WHERE boolean_column filters true rows [OK]

Hint: WHERE boolean_column filters true rows directly [OK]

Common Mistakes:

• Thinking WHERE boolean_column filters false rows
• Expecting syntax error without explicit comparison
• Assuming it returns all rows

4. Identify the error in this query that tries to select rows where is_active is false:

SELECT * FROM accounts WHERE is_active = FALSE;

medium

A. No error, query is correct

B. Should use WHERE NOT is_active instead

C. Boolean columns cannot be compared with FALSE

D. Missing parentheses around FALSE

Solution

1. Step 1: Check boolean comparison syntax

   In PostgreSQL, comparing a boolean column with FALSE using = FALSE is valid syntax.

2. Step 2: Confirm query correctness

   The query correctly filters rows where is_active is false. Using WHERE NOT is_active is an alternative but not required.

3. Final Answer:

   No error, query is correct -> Option A

4. Quick Check:

   Boolean = FALSE is valid syntax [OK]

Hint: Boolean = FALSE is valid; NOT is optional [OK]

Common Mistakes:

• Thinking boolean cannot be compared with FALSE
• Believing parentheses are needed around FALSE
• Confusing NOT with = FALSE as mandatory

5. You want to select all rows from orders where the boolean column shipped is false or NULL. Which query correctly achieves this?

hard

A. SELECT * FROM orders WHERE NOT shipped;

B. SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL;

C. SELECT * FROM orders WHERE shipped = FALSE;

D. SELECT * FROM orders WHERE shipped IS NULL;

Solution

1. Step 1: Understand boolean and NULL filtering

   Boolean columns can be true, false, or NULL. To include false or NULL, both conditions must be checked.

2. Step 2: Analyze each option

   SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL; explicitly checks for false or NULL, correctly including both. SELECT * FROM orders WHERE NOT shipped; filters only false but excludes NULL. SELECT * FROM orders WHERE shipped = FALSE; excludes NULL. SELECT * FROM orders WHERE shipped IS NULL; filters only NULL, missing false rows.

3. Final Answer:

   SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL; -> Option B

4. Quick Check:

   Include false and NULL with OR condition [OK]

Hint: Use OR with IS NULL to include false or NULL [OK]

Common Mistakes:

• Using NOT shipped excludes NULL rows
• Ignoring NULL values in boolean filtering
• Assuming shipped = FALSE includes NULL