Boolean column filtering patterns in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Filter rows where a boolean column is TRUE

Given a table tasks with a boolean column completed, which query returns only the rows where completed is TRUE?

PostgreSQL

SELECT * FROM tasks WHERE completed = TRUE;

A SELECT * FROM tasks WHERE completed = TRUE;

B SELECT * FROM tasks WHERE completed = 'true';

C SELECT * FROM tasks WHERE completed = 1;

D SELECT * FROM tasks WHERE completed IS TRUE;

💡 Hint

In PostgreSQL, boolean values are TRUE or FALSE, not numbers or strings.

Option D uses the correct SQL syntax to filter boolean TRUE values. Option D works but is less idiomatic. Option D compares to a string, which is invalid. Option D compares to a number, which is invalid in PostgreSQL boolean context.

❓ query_result

intermediate

Filter rows where a boolean column is FALSE

Which query correctly returns rows where the boolean column active is FALSE in PostgreSQL?

PostgreSQL

SELECT * FROM users WHERE active = FALSE;

A SELECT * FROM users WHERE active IS FALSE;

B SELECT * FROM users WHERE active = 'false';

C SELECT * FROM users WHERE active = FALSE;

D SELECT * FROM users WHERE active = 0;

💡 Hint

Use the IS operator for boolean checks in PostgreSQL.

Option A is the correct and idiomatic way to check for FALSE boolean values. Option A works but is less preferred. Options B and D compare to string and number, which are invalid for boolean columns.

📝 Syntax

advanced

Identify the syntax error in boolean filtering

Which option contains a syntax error when filtering a boolean column is_verified for TRUE values?

PostgreSQL

SELECT * FROM accounts WHERE is_verified = TRUE;

A SELECT * FROM accounts WHERE is_verified IS TRUE;

B SELECT * FROM accounts WHERE is_verified IS NOT FALSE;

C SELECT * FROM accounts WHERE is_verified = 'TRUE';

D SELECT * FROM accounts WHERE is_verified = TRUE;

💡 Hint

Boolean values in PostgreSQL are not strings.

Option C compares the boolean column to a string 'TRUE', which causes a syntax error. The other options use valid boolean expressions.

❓ query_result

advanced

Filter rows where boolean column is NULL or FALSE

Given a boolean column subscribed that can be TRUE, FALSE, or NULL, which query returns rows where subscribed is either FALSE or NULL?

PostgreSQL

SELECT * FROM newsletter WHERE subscribed IS FALSE OR subscribed IS NULL;

A SELECT * FROM newsletter WHERE subscribed IS FALSE OR subscribed IS NULL;

B SELECT * FROM newsletter WHERE subscribed = FALSE OR subscribed IS NULL;

C SELECT * FROM newsletter WHERE subscribed = FALSE OR subscribed = NULL;

D SELECT * FROM newsletter WHERE NOT subscribed;

💡 Hint

Use IS NULL to check for NULL values in SQL.

Option A correctly uses IS FALSE and IS NULL to filter both FALSE and NULL values. Option A mixes = FALSE (valid) with IS NULL (valid), but = FALSE can behave differently if NULLs exist. Option A uses = NULL which is invalid. Option A excludes NULLs because NOT NULL is unknown.

🧠 Conceptual

expert

Understanding boolean filtering with NOT operator

What is the result of this query on a table orders with a boolean column shipped that can be TRUE, FALSE, or NULL?

SELECT * FROM orders WHERE NOT shipped;

A Returns rows where shipped is FALSE or NULL

B Returns rows where shipped is FALSE only

C Returns rows where shipped is NULL only

D Returns rows where shipped is TRUE only

💡 Hint

In SQL, NOT NULL evaluates to UNKNOWN, which is treated as false in WHERE.

The NOT operator applied to NULL results in UNKNOWN, which filters out those rows. So only rows where shipped is FALSE are returned.

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