Three-valued logic (TRUE, FALSE, UNKNOWN) in SQL - Mini Project: Build & Apply

Understanding Three-Valued Logic in SQL

📖 Scenario: You are working with a database that stores information about employees and their project assignments. Some employees may not have been assigned to any project yet, so their project assignment is unknown (NULL).We want to understand how SQL handles conditions when some values are NULL, using three-valued logic: TRUE, FALSE, and UNKNOWN.

🎯 Goal: Build a simple SQL table with employee data including NULL values, then write queries that demonstrate how SQL evaluates conditions with TRUE, FALSE, and UNKNOWN results.

📋 What You'll Learn

Create a table called employees with columns id (integer), name (text), and project (text, can be NULL).

Insert exactly these rows into employees: (1, 'Alice', 'ProjectX'), (2, 'Bob', NULL), (3, 'Charlie', 'ProjectY').

Create a variable or temporary table to hold a condition that checks if project equals 'ProjectX'.

Write a SELECT query that uses the condition to filter employees and shows how three-valued logic works with NULLs.

💡 Why This Matters

🌍 Real World

Handling NULL values and understanding three-valued logic is essential when working with real databases where data may be incomplete or missing.

💼 Career

Database developers and analysts must know how SQL treats NULLs to write correct queries and avoid unexpected results in reports and applications.

1

Create the employees table and insert data

Write SQL statements to create a table called employees with columns id (integer), name (text), and project (text, nullable). Then insert these exact rows: (1, 'Alice', 'ProjectX'), (2, 'Bob', NULL), and (3, 'Charlie', 'ProjectY').

SQL

-- Create the employees table and insert the rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as specified.

2

Define a condition to check for project 'ProjectX'

Write a SQL statement that defines a condition checking if the project column equals 'ProjectX'. Use this condition in a WHERE clause or as a named expression. For example, create a view or a common table expression (CTE) named project_check that selects all columns and adds a column is_project_x which is TRUE if project = 'ProjectX', FALSE otherwise, and UNKNOWN if project is NULL.

SQL

-- Define a condition to check if project equals 'ProjectX'
-- Your code here

Need a hint?

Use a WITH clause to create a CTE that adds a boolean column showing the result of project = 'ProjectX'.

3

Write a query to select employees where project is 'ProjectX'

Write a SQL SELECT query that uses the project_check CTE and filters rows where is_project_x is TRUE. This will show which employees have project 'ProjectX' according to three-valued logic.

SQL

-- Select employees where is_project_x is TRUE
-- Your code here

Need a hint?

Use WHERE is_project_x = TRUE to filter only rows where the condition is true.

4

Complete the query to show all employees with their condition results

Write a SQL query that selects all employees and shows the value of project = 'ProjectX' for each row, demonstrating TRUE, FALSE, and UNKNOWN results. Use the project_check CTE and select id, name, project, and is_project_x columns.

SQL

-- Select all employees with their is_project_x values
-- Your code here

Need a hint?

Select all columns including the condition column to see TRUE, FALSE, and UNKNOWN values.