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.