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

Need a 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

Need a 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

Need a 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

Need a hint?

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