Three-valued logic (TRUE, FALSE, UNKNOWN) in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select rows where the condition is TRUE in three-valued logic.

SQL

SELECT * FROM employees WHERE active IS [1];

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

ATRUE

BNULL

CUNKNOWN

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL instead of TRUE

Using = TRUE instead of IS TRUE

In SQL three-valued logic, to check if a condition is true, use IS TRUE.

2fill in blank

medium

Complete the code to select rows where the condition is UNKNOWN in three-valued logic.

SQL

SELECT * FROM orders WHERE shipped_date IS [1];

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

AFALSE

BUNKNOWN

CTRUE

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using IS NULL instead of IS UNKNOWN

Using IS FALSE instead of IS UNKNOWN

In SQL, IS UNKNOWN checks if the condition evaluates to unknown (NULL).

3fill in blank

hard

Fix the error in the WHERE clause to correctly filter rows where the condition is FALSE.

SQL

SELECT * FROM products WHERE discontinued IS [1];

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

ATRUE

BUNKNOWN

CFALSE

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using = FALSE instead of IS FALSE

Using IS NULL instead of IS FALSE

To filter rows where a condition is false in three-valued logic, use IS FALSE.

4fill in blank

hard

Fill both blanks to complete the query that selects rows where the condition is either TRUE or UNKNOWN.

SQL

SELECT * FROM customers WHERE status IS [1] OR status IS [2];

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

ATRUE

BFALSE

CUNKNOWN

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using IS NULL instead of IS UNKNOWN

Using IS FALSE instead of IS UNKNOWN

This query selects rows where status is either TRUE or UNKNOWN, using IS TRUE and IS UNKNOWN.

5fill in blank

hard

Fill all three blanks to create a CASE expression that returns 'Yes', 'No', or 'Unknown' based on a three-valued logic condition.

SQL

SELECT CASE WHEN approved IS [1] THEN 'Yes' WHEN approved IS [2] THEN 'No' ELSE [3] END AS approval_status FROM applications;

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

ATRUE

BFALSE

C'Unknown'

D'Pending'

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL instead of a string for ELSE

Mixing TRUE and FALSE in wrong order

The CASE expression checks if approved is TRUE or FALSE, returning 'Yes' or 'No'. Otherwise, it returns 'Unknown'.

Practice

1. In SQL's three-valued logic, what is the result of comparing a column value to NULL using the = operator?

easy

A. TRUE

B. NULL

C. FALSE

D. UNKNOWN

Solution

1. Step 1: Understand comparison with NULL

   In SQL, any comparison with NULL using = does not return TRUE or FALSE but UNKNOWN.

2. Step 2: Recall three-valued logic rules

   Since NULL means unknown, the comparison result is UNKNOWN, not TRUE or FALSE.

3. Final Answer:

   UNKNOWN -> Option D

4. Quick Check:

   Comparison with NULL = UNKNOWN [OK]

Hint: Comparing with NULL using '=' always yields UNKNOWN [OK]

Common Mistakes:

• Assuming NULL equals NULL returns TRUE
• Thinking NULL comparison returns FALSE
• Confusing NULL with a string 'NULL'

2. Which of the following SQL WHERE clause conditions correctly checks if a column col contains NULL values?

easy

A. WHERE col = NULL

B. WHERE col != NULL

C. WHERE col IS NULL

D. WHERE col = 'NULL'

Solution

1. Step 1: Identify correct NULL check syntax

   In SQL, to check for NULL values, you must use IS NULL because = NULL does not work.

2. Step 2: Evaluate each option

   WHERE col IS NULL uses IS NULL, which is the correct syntax. Others compare with NULL using = or treat NULL as a string, which is incorrect.

3. Final Answer:

   WHERE col IS NULL -> Option C

4. Quick Check:

   Use IS NULL to check NULL values [OK]

Hint: Use IS NULL, not = NULL, to check for NULL values [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Comparing NULL as a string 'NULL'
• Using != NULL to find non-NULL values

3. Given a table Employees with a column Bonus that can be NULL, what will the query SELECT * FROM Employees WHERE Bonus > 1000; return for rows where Bonus is NULL?

medium

A. Rows with NULL Bonus are excluded

B. Rows with NULL Bonus are included

C. Query returns an error

D. Rows with NULL Bonus are returned as FALSE

Solution

1. Step 1: Understand comparison with NULL in WHERE clause

   Comparing NULL with any value using > results in UNKNOWN, which is treated as FALSE in WHERE filtering.

2. Step 2: Determine which rows are returned

   Rows where Bonus is NULL do not satisfy Bonus > 1000 condition, so they are excluded from the result.

3. Final Answer:

   Rows with NULL Bonus are excluded -> Option A

4. Quick Check:

   NULL comparison in WHERE excludes rows [OK]

Hint: WHERE excludes rows with NULL in condition comparisons [OK]

Common Mistakes:

• Assuming NULL > 1000 returns TRUE
• Expecting NULL rows to appear in results
• Thinking query throws error due to NULL

4. You wrote this query: SELECT * FROM Orders WHERE Status = 'Shipped' OR Status <> 'Shipped'; but it returns fewer rows than expected. What is the likely cause?

medium

A. Rows with NULL Status are excluded due to three-valued logic

B. Syntax error in the query

C. The OR operator is invalid here

D. The query returns all rows regardless of Status

Solution

1. Step 1: Analyze the WHERE clause logic

   The condition checks if Status equals 'Shipped' OR Status not equals 'Shipped'.

2. Step 2: Consider NULL values in Status

   For rows where Status is NULL, both comparisons return UNKNOWN, so those rows are excluded.

3. Final Answer:

   Rows with NULL Status are excluded due to three-valued logic -> Option A

4. Quick Check:

   NULL comparisons exclude rows in OR conditions [OK]

Hint: NULL in OR conditions can exclude rows unexpectedly [OK]

Common Mistakes:

• Assuming NULL rows satisfy OR condition
• Thinking query has syntax error
• Believing OR always returns all rows

5. You want to select all rows from Products where Discount is either greater than 20 or unknown (NULL). Which WHERE clause correctly implements this using three-valued logic?

hard

A. WHERE Discount > 20 OR Discount = NULL

B. WHERE Discount > 20 OR Discount IS NULL

C. WHERE Discount > 20 AND Discount IS NULL

D. WHERE Discount >= 20 OR Discount IS NOT NULL

Solution

1. Step 1: Identify correct NULL check syntax

   To check for NULL values, use IS NULL, not = NULL.

2. Step 2: Combine conditions correctly

   You want rows where Discount is greater than 20 OR Discount is NULL, so use OR with Discount > 20 and Discount IS NULL.

3. Final Answer:

   WHERE Discount > 20 OR Discount IS NULL -> Option B

4. Quick Check:

   Use IS NULL with OR for NULL checks [OK]

Hint: Use IS NULL with OR to include NULLs in conditions [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Using AND instead of OR
• Checking IS NOT NULL instead of IS NULL