What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed-length, always uses n bytes regardless of actual content. VARCHAR(n) is variable-length, uses only bytes needed (1-n bytes).

CHAR: Use for fixed codes (country codes "US", "UK", state codes)

VARCHAR: Use for variable text (names, emails, addresses)

Example: CHAR(2) for "US" uses 2 bytes. VARCHAR(100) for "John" uses 5 bytes (not 100).

Why should you use DECIMAL instead of FLOAT for money?

FLOAT has binary rounding errors that compound over millions of transactions:

FLOAT: 10.10 becomes 10.099999904632568 (WRONG!)

DECIMAL(10,2): 10.10 stays exactly 10.10 (CORRECT!)

Financial regulations require exact precision. FLOAT errors cause:

• Audit failures

• Customer billing errors

• Legal compliance issues

Always use DECIMAL for money, prices, tax calculations.

Explain PRIMARY KEY vs UNIQUE constraint

PRIMARY KEY = UNIQUE + NOT NULL + one per table

UNIQUE = no duplicates allowed, can be NULL, multiple per table

PRIMARY KEY:

• Identifies each row uniquely

• Cannot be NULL

• Only ONE per table

• Automatically indexed

• Example: employee_id, order_id

UNIQUE:

• Prevents duplicates

• Can be NULL (NULL ≠ NULL)

• Multiple per table

• Example: email, username, SSN

When should you use NOT NULL vs allowing NULLs?

Use NOT NULL for:

• Required business data (first_name, email, price)

• Fields that must always have value

• Better query performance (no NULL checks)

Allow NULL for:

• Optional fields (middle_name, phone)

• Unknown values (end_date for current employees)

• Relationships that may not exist (manager_id for CEO)

Rule: If field is truly required by business logic, use NOT NULL.