Boolean column filtering patterns in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select rows where the boolean column is_active is true.

PostgreSQL

SELECT * FROM users WHERE is_active [1];

Drag options to blanks, or click blank then click option'

AIS NULL

B= TRUE

C= FALSE

DIS NOT TRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using = FALSE instead of = TRUE

Using IS NULL which checks for null values

Using IS NOT TRUE which excludes true values

In PostgreSQL, to filter rows where a boolean column is true, you can use = TRUE.

2fill in blank

medium

Complete the code to select rows where the boolean column is_verified is false.

PostgreSQL

SELECT * FROM accounts WHERE is_verified [1];

Drag options to blanks, or click blank then click option'

A= FALSE

BIS TRUE

C= TRUE

DIS NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using = TRUE instead of = FALSE

Using IS NULL which checks for null values

Using IS TRUE which checks for true values

To filter rows where a boolean column is false, use = FALSE.

3fill in blank

hard

Fix the error in the code to select rows where the boolean column is_deleted is not true.

PostgreSQL

SELECT * FROM records WHERE is_deleted [1];

Drag options to blanks, or click blank then click option'

A= FALSE

B= NULL

CIS NOT TRUE

DIS FALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using = FALSE excludes nulls

Using = NULL is invalid syntax

Using IS FALSE excludes nulls

Using IS NOT TRUE correctly filters rows where the boolean is false or null.

4fill in blank

hard

Fill both blanks to select rows where is_published is true and is_archived is false.

PostgreSQL

SELECT * FROM articles WHERE is_published [1] AND is_archived [2];

Drag options to blanks, or click blank then click option'

A= TRUE

B= FALSE

CIS TRUE

DIS FALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using IS TRUE or IS FALSE inconsistently

Mixing up true and false checks

Using IS NULL instead of boolean checks

Use = TRUE to check for true and = FALSE to check for false explicitly.

5fill in blank

hard

Fill all three blanks to select rows where is_active is true, is_verified is not true, and is_deleted is false.

PostgreSQL

SELECT * FROM users WHERE is_active [1] AND is_verified [2] AND is_deleted [3];

Drag options to blanks, or click blank then click option'

A= TRUE

BIS NOT TRUE

C= FALSE

DIS TRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using = FALSE for is_active

Using IS TRUE for is_verified instead of IS NOT TRUE

Using IS NOT TRUE for is_deleted

This query filters for active users who are not verified and not deleted by combining the correct boolean checks.

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