Boolean type behavior in PostgreSQL - Mini Project: Build & Apply

Boolean Type Behavior in PostgreSQL

📖 Scenario: You are managing a simple task tracking system. Each task can be marked as completed or not completed using a boolean value.

🎯 Goal: Build a PostgreSQL table to store tasks with a boolean column indicating completion status. Then, write queries to insert tasks, update their status, and select tasks based on their completion.

📋 What You'll Learn

Create a table named tasks with columns id (integer primary key), description (text), and completed (boolean).

Insert three tasks with specific descriptions and completion statuses.

Write a query to select all tasks that are not completed.

Write a query to update a task's completion status to true.

💡 Why This Matters

🌍 Real World

Boolean columns are commonly used in databases to track yes/no, true/false, or completed/not completed states for records.

💼 Career

Understanding boolean data types and how to query and update them is essential for database management and backend development roles.

1

Create the tasks table

Write a SQL statement to create a table called tasks with three columns: id as an integer primary key, description as text, and completed as a boolean.

PostgreSQL

-- Create the tasks table with id, description, and completed columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified column names and types.

2

Insert tasks with boolean completion status

Insert three rows into the tasks table with these exact values: (1, 'Buy groceries', false), (2, 'Clean the house', true), and (3, 'Pay bills', false).

PostgreSQL

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    description TEXT,
    completed BOOLEAN
);
-- Insert the three tasks below
-- Your code here

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Select tasks that are not completed

Write a SQL query to select all columns from tasks where the completed column is false.

PostgreSQL

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    description TEXT,
    completed BOOLEAN
);

INSERT INTO tasks (id, description, completed) VALUES
(1, 'Buy groceries', false),
(2, 'Clean the house', true),
(3, 'Pay bills', false);

-- Select tasks where completed is false
-- Your code here

Need a hint?

Use WHERE completed = false to filter tasks not done.

4

Update a task's completion status

Write a SQL statement to update the completed column to true for the task with id equal to 3.

PostgreSQL

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    description TEXT,
    completed BOOLEAN
);

INSERT INTO tasks (id, description, completed) VALUES
(1, 'Buy groceries', false),
(2, 'Clean the house', true),
(3, 'Pay bills', false);

SELECT * FROM tasks WHERE completed = false;

-- Update task with id 3 to completed true
-- Your code here

Need a hint?

Use UPDATE with SET completed = true and a WHERE clause for id = 3.