SQL Data Type-tutorial
When we create a table in SQL, every column must have a data type.
A data type defines what kind of values can be stored in that column for example, numbers, text, or dates.
What is a Data Type?
In simple terms, a Data Type tells the database what kind of information can be stored in a particular column.
Every column in a table must have a data type it acts like a label that tells MySQL,
“Hey, this column will store numbers,” or “This one will hold text,” or “This one is for dates.”
Without data types, the database would not know how to store or process your information correctly.
Why Data Types Are Important
- Accuracy: Ensures only valid data is stored (e.g., numbers in numeric columns).
- Efficiency: Saves space by storing data in the correct format.
- Validation: Prevents incorrect entries (like inserting text into a number column).
- Performance: Helps SQL process queries faster and more effectively.
Example
CREATE TABLE Students ( StudentID INT, Name VARCHAR(50), Age INT, City VARCHAR(50), AdmissionDate DATE);Here
- INT - For whole numbers
- VARCHAR(50) - For text (up to 50 characters)
- DATE - For storing date values
Common SQL Data Types (MySQL)
1. Numeric Data Types
Used to store numbers (with or without decimals).
| Data Type | Description | Example |
|---|---|---|
| INT | Whole numbers (positive or negative) | 5, -100, 2000 |
| DECIMAL | Fixed-point numbers with decimals | 12.50, 99.99 |
| FLOAT / DOUBLE | Numbers with floating decimals (approximate) | 3.14, -0.45 |
| BIGINT | Very large integers | 9876543210 |
Use INT for IDs or counts, and DECIMAL / FLOAT for prices or measurements.
2. String (Text) Data Types
| Data Type | Description | Example |
|---|---|---|
| CHAR (n) | Fixed-length text (always reserves n spaces) | 'YES', 'NO' |
| VARCHAR (n) | Variable-length text (saves space) | 'Amit Kumar' |
| TEXT | Large text blocks | 'This is a long paragraph' |
VARCHAR is the most common flexible and efficient for most text data.
3. Date and Time Data Types
| Data Type | Description | Example |
|---|---|---|
| DATE | Stores date in YYYY-MM-DD format | 2025-10-10 |
| TIME | Stores time in HH:MM:SS | 14:30:00 |
| DATETIME | Stores both date and time | 2025-10-10 14:30:00 |
| YEAR | Stores only year | 2025 |
Perfect for recording when an order was placed or a student joined.
4. Boolean and Miscellaneous Data Types
| Data Type | Description | Example |
|---|---|---|
| BOOLEAN / BOOL | True or False values | TRUE, FALSE |
| ENUM | Fixed set of allowed values | ENUM ('Male', 'Female', 'Other') |
| BLOB | Binary data (images, files) | Image files, docs |
Use ENUM for limited choices and BLOB for storing media or documents.
Choosing the Right Data Type
- Use INT for numeric values like IDs or counts.
- Use VARCHAR for names, emails, or addresses.
- Use DATE or DATETIME for time-based data.
- Use BOOLEAN for true/false conditions.
- Use DECIMAL or FLOAT for prices or measurements.
Example: Student Table
CREATE TABLE Students ( StudentID INT, Name VARCHAR(50), Age INT, City VARCHAR(50), AdmissionDate DATE, FeesPaid DECIMAL(8,2), IsActive BOOLEAN);Explanation
- StudentID - Unique student number
- Name - Text up to 50 characters
- FeesPaid - Decimal value (like ₹5000.50)
- IsActive - TRUE or FALSE (active/inactive status)
Two Minute Drill
- Data Types define the kind of data each column stores
- Common types --> INT, VARCHAR, DATA, DECIMAL, BOOLEAN
- VARCHAR is used for text, INT for numbers, DATE for data values
- Always choose the right type for efficiency and accuracy
- MySQL will reject data that does not match its column's type