What are standard SQL Data Types (VARCHAR vs CHAR, INT, DECIMAL, DATETIME)?
❓ Interview Prompt: Compare SQL Data Types: CHAR vs VARCHAR, INT vs BIGINT, DECIMAL (exact precision) vs FLOAT, and DATETIME vs TIMESTAMP.
Selecting appropriate SQL data types optimizes storage space and prevents data truncation:
| Data Type | Description & Characteristics |
|---|---|
| 'CHAR(N)' | Fixed-length string (padded with spaces). Fast lookups for fixed strings (e.g. ISO country code ''US''). |
| 'VARCHAR(N)' | Variable-length string. Conserves storage space; best for names and emails. |
| 'INT' vs 'BIGINT' | 'INT' (4 bytes, max ~2 billion); 'BIGINT' (8 bytes, required for primary key IDs). |
| 'DECIMAL(P, D)' | Exact fixed-point precision (e.g. 'DECIMAL(10, 2)' for currency; never use 'FLOAT' for money!). |
| 'DATETIME' vs 'TIMESTAMP' | 'DATETIME' (Fixed calendar date); 'TIMESTAMP' (UTC converted based on server timezone). |
Code Implementation Example
CREATE TABLE products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
sku CHAR(8) NOT NULL, -- Fixed 8-char code
title VARCHAR(255) NOT NULL, -- Variable string
price DECIMAL(10, 2) NOT NULL, -- Exact currency precision
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Use DECIMAL for currency calculations to prevent floating-point rounding errors, and BIGINT for auto-incrementing Primary Keys.