Cambridge Syllabus Notes

18 Databases – Boolean Field Display Formats

A Boolean field can only hold two values: True or False. Think of it like a light switch – it’s either ON or OFF ⚙️.

In many databases the values are stored as numbers: $1$ for $True$ and $0$ for $False$.

Different database tools let you choose which format you want to see when you look at the data.

⚙️ Define the field type as BOOLEAN (or BIT(1), TINYINT(1), NUMBER(1), etc.) in the table schema.
📊 Choose the display format in your database client:
- In Microsoft Access – right‑click the field → Format → pick Yes/No, True/False or Checkbox.
- In MySQL Workbench – set the DEFAULT to TRUE or FALSE and use IF or CASE in queries to show Yes/No.
- In PostgreSQL – the boolean type already shows true/false; use CASE to change to Yes/No.

📝 Write a query to display the chosen format:

SELECT
  student_id,
  name,
  CASE WHEN is_enrolled THEN 'Yes' ELSE 'No' END AS Enrolled
FROM Students;

DBMS	Boolean Type	Display Format Example
MySQL	BOOLEAN (alias for TINYINT(1))	`SELECT IF(is_active, '✔', '✘') AS Status FROM Users;`
PostgreSQL	boolean	`SELECT CASE WHEN is_admin THEN 'Yes' ELSE 'No' END AS Admin FROM Roles;`
SQLite	INTEGER (0 or 1)	`SELECT IFNULL(is_published, 0) AS Published FROM Articles;`
Oracle	NUMBER(1)	`SELECT DECODE(is_active, 1, 'Yes', 'No') AS Active FROM Employees;`

?? Know the syntax for CASE and IF in the DBMS you are using.
?? Remember the storage format – MySQL uses TINYINT(1), PostgreSQL uses boolean, Oracle uses NUMBER(1).
?? Use the correct literals – TRUE/FALSE, 1/0, or 'Yes'/'No' depending on the question.
?? Check for NULL values – decide if a NULL should be treated as FALSE or left blank.
?? Practice writing queries that convert a Boolean field to a readable format for reports.