MySQL Data Types | Numeric, String, Date and Time Data Types | Complete Notes | CBSE Class 11 Informatics Practices (2026–27)
Class 11 · Informatics Practices
MySQL Data Types | Numeric, String, Date and Time Data Types
Before creating a table in MySQL, it is important to decide what type of data each column will store. This is done using data types. A data type tells MySQL what kind of values are allowed in a column, such as numbers, text, dates, or time. Choosing the correct data type improves storage efficiency, maintains data accuracy, and prevents invalid data from being entered.
For example, a student's name should be stored as text, marks as numbers, and date of birth as a date. Selecting appropriate data types is one of the first steps in designing an efficient database.
Learning Outcomes
After studying this chapter, you will be able to:
- Understand the concept of MySQL data types.
- Classify data types into Numeric, String, Date, and Time categories.
- Differentiate between CHAR and VARCHAR.
- Select appropriate data types for different kinds of data.
- Understand the importance of choosing suitable data types.
What is a Data Type?
A data type specifies the type of values that can be stored in a table column. It defines the nature of the data, such as numbers, text, dates, or time.
Why Are Data Types Important?
- Ensure only valid data is stored.
- Reduce storage requirements.
- Improve database performance.
- Maintain data consistency.
- Prevent data entry errors.
Categories of MySQL Data Types
| Category | Purpose | Examples |
|---|---|---|
| Numeric | Stores numbers. | INT, FLOAT |
| String | Stores text. | CHAR, VARCHAR |
| Date | Stores dates. | DATE, YEAR |
| Time | Stores time values. | TIME |
1. Numeric Data Types
Numeric data types are used to store integer and decimal values.
INT
The INT data type stores whole numbers without decimal places.
Examples
- Roll Number
- Age
- Number of Students
- Quantity
| Value | Valid? |
|---|---|
| 25 | ✔ Yes |
| 1500 | ✔ Yes |
| 45.8 | ✘ No |
FLOAT
The FLOAT data type stores numbers with decimal values.
Examples
- Percentage
- Height
- Weight
- Price
| Value | Valid? |
|---|---|
| 85.50 | ✔ Yes |
| 175.25 | ✔ Yes |
| 500 | ✔ Yes |
2. String Data Types
String data types store text, words, names, addresses, and other character-based information.
CHAR
The CHAR data type stores fixed-length character strings.
Examples
- Gender (M/F)
- Country Code
- State Code
If CHAR(10) is declared and only 5 characters are stored, MySQL automatically fills the remaining space with blank characters.
VARCHAR
The VARCHAR data type stores variable-length character strings.
Examples
- Student Name
- City
- Email Address
- School Name
VARCHAR stores only the number of characters actually entered, making it more storage-efficient for text of varying lengths.
CHAR vs VARCHAR
| Feature | CHAR | VARCHAR |
|---|---|---|
| Length | Fixed | Variable |
| Storage | Uses fixed space. | Uses only required space. |
| Suitable For | Short fixed values. | Names and long text. |
| Example | Gender | Student Name |
Real-Life Example
| Column | Suitable Data Type |
|---|---|
| Roll_No | INT |
| Name | VARCHAR(50) |
| Gender | CHAR(1) |
| Percentage | FLOAT |
3. Date Data Types
Date data types are used to store calendar-related information such as dates of birth, admission dates, and joining dates.
DATE
The DATE data type stores a date in the format YYYY-MM-DD.
Examples
- Date of Birth
- Admission Date
- Joining Date
- Examination Date
| Value | Valid? |
|---|---|
| 2026-07-25 | ✔ Yes |
| 2025-01-10 | ✔ Yes |
| 25-07-2026 | ✘ No (Incorrect Format) |
YEAR
The YEAR data type stores only the year in four-digit format.
Examples
- Year of Admission
- Year of Passing
- Manufacturing Year
| Value | Valid? |
|---|---|
| 2026 | ✔ Yes |
| 2024 | ✔ Yes |
| 26 | ✘ No |
4. Time Data Type
TIME
The TIME data type stores time values in the format HH:MM:SS.
Examples
- School Reporting Time
- Examination Start Time
- Office Login Time
- Train Departure Time
| Value | Valid? |
|---|---|
| 08:30:00 | ✔ Yes |
| 14:15:45 | ✔ Yes |
| 8:30 | ✘ No (Incorrect Format) |
Comparison of Common MySQL Data Types
| Data Type | Stores | Example |
|---|---|---|
| INT | Whole numbers | 25 |
| FLOAT | Decimal numbers | 92.75 |
| CHAR | Fixed-length text | M |
| VARCHAR | Variable-length text | Rahul Sharma |
| DATE | Date | 2026-07-25 |
| TIME | Time | 09:30:00 |
| YEAR | Year | 2026 |
Choosing the Correct Data Type
Selecting the appropriate data type improves database performance and ensures that only valid information is stored.
| Information | Recommended Data Type |
|---|---|
| Student Roll Number | INT |
| Student Name | VARCHAR(50) |
| Gender | CHAR(1) |
| Percentage | FLOAT |
| Date of Birth | DATE |
| Admission Year | YEAR |
| School Reporting Time | TIME |
Solved Example 1
Choose the most suitable data type.
| Column | Answer |
|---|---|
| Age | INT |
| Height | FLOAT |
| Student Name | VARCHAR |
| Gender | CHAR |
| Date of Birth | DATE |
| Reporting Time | TIME |
Solved Example 2
Identify whether the following values are valid.
| Value | Suitable Data Type | Valid? |
|---|---|---|
| 98 | INT | ✔ Yes |
| 87.65 | FLOAT | ✔ Yes |
| 2026-10-18 | DATE | ✔ Yes |
| 18/10/2026 | DATE | ✘ No |
| 09:45:20 | TIME | ✔ Yes |
Common Errors
| Mistake | Correct Understanding |
|---|---|
| Using INT to store decimal values. | Use FLOAT for decimal numbers. |
| Using CHAR for long names. | VARCHAR is more suitable for variable-length text. |
| Writing dates as DD-MM-YYYY. | MySQL DATE uses YYYY-MM-DD format. |
| Using VARCHAR for numeric calculations. | Numeric values should use INT or FLOAT. |
| Using incorrect TIME format. | TIME should be stored as HH:MM:SS. |
Interview Corner
Q. When should you use CHAR instead of VARCHAR?
Answer: Use CHAR for data with a fixed length, such as gender codes (M/F) or country codes. Use VARCHAR for data whose length varies, such as names, addresses, or email IDs.
Real-Life Applications of MySQL Data Types
| Application | Column | Suitable Data Type |
|---|---|---|
| School Management System | Roll Number | INT |
| School Management System | Student Name | VARCHAR(50) |
| School Management System | Percentage | FLOAT |
| Hospital Management System | Patient Name | VARCHAR(100) |
| Banking System | Account Balance | FLOAT |
| Library Management System | Issue Date | DATE |
| Railway Reservation System | Departure Time | TIME |
| University Database | Admission Year | YEAR |
Solved Example 3
Select the most appropriate MySQL data type for each column.
| Column Name | Answer |
|---|---|
| Employee_ID | INT |
| Employee_Name | VARCHAR(50) |
| Salary | FLOAT |
| Date_of_Joining | DATE |
| Office_Start_Time | TIME |
Solved Example 4
Identify the correct data type.
| Value | Suitable Data Type |
|---|---|
| 2027 | YEAR |
| 15:30:00 | TIME |
| 2026-12-01 | DATE |
| 98.75 | FLOAT |
| Subodh Public School | VARCHAR |
Solved Example 5
Question:
A table contains the following columns:
- Book_ID
- Book_Name
- Price
- Publication_Year
Choose suitable MySQL data types.
| Column | Data Type |
|---|---|
| Book_ID | INT |
| Book_Name | VARCHAR(100) |
| Price | FLOAT |
| Publication_Year | YEAR |
Competency-Based Questions
- A school is creating a Student table with the columns Roll_No, Name, Date_of_Birth, Percentage, Gender, and Admission_Year. Suggest the most appropriate MySQL data type for each column with reasons.
- Differentiate between CHAR and VARCHAR with suitable examples.
- Why is VARCHAR preferred over CHAR for storing student names?
- A company stores employee joining dates and office reporting times. Which MySQL data types should be used? Explain your answer.
- Why is selecting the correct data type important while designing a database?
Multiple Choice Questions
- Which MySQL data type stores whole numbers?
- (a) FLOAT
- (b) INT
- (c) VARCHAR
- (d) DATE
- Which data type is used to store decimal values?
- (a) INT
- (b) FLOAT
- (c) YEAR
- (d) CHAR
- Which data type stores variable-length text?
- (a) CHAR
- (b) VARCHAR
- (c) INT
- (d) TIME
- The DATE data type stores values in the format:
- (a) DD-MM-YYYY
- (b) MM-DD-YYYY
- (c) YYYY-MM-DD
- (d) YYYY/DD/MM
- Which data type stores only the year?
- (a) DATE
- (b) TIME
- (c) YEAR
- (d) INT
- The TIME data type stores values in the format:
- (a) HH-MM-SS
- (b) HH:MM:SS
- (c) MM:HH:SS
- (d) HH/MM/SS
- Which data type is most suitable for storing gender (M/F)?
- (a) VARCHAR
- (b) CHAR
- (c) FLOAT
- (d) DATE
- Which data type is best suited for storing email addresses?
- (a) INT
- (b) CHAR
- (c) VARCHAR
- (d) YEAR
- Which statement about VARCHAR is correct?
- (a) It stores only numbers.
- (b) It stores fixed-length text.
- (c) It stores variable-length text.
- (d) It stores only dates.
- Select the most appropriate data type for storing examination marks such as 89.75.
- (a) CHAR
- (b) DATE
- (c) FLOAT
- (d) YEAR
Quick Revision
| Data Type | Stores |
|---|---|
| INT | Whole numbers |
| FLOAT | Decimal numbers |
| CHAR | Fixed-length text |
| VARCHAR | Variable-length text |
| DATE | Date (YYYY-MM-DD) |
| TIME | Time (HH:MM:SS) |
| YEAR | Four-digit year |
Important Points to Remember
- A data type specifies the kind of values that can be stored in a table column.
- Use INT for whole numbers and FLOAT for decimal numbers.
- CHAR stores fixed-length text, whereas VARCHAR stores variable-length text.
- The DATE data type stores dates in the YYYY-MM-DD format.
- The TIME data type stores time in the HH:MM:SS format.
- The YEAR data type stores only the year.
- Selecting the correct data type improves storage efficiency, performance, and data integrity.
- VARCHAR is generally preferred over CHAR for storing names, addresses, and email IDs.
CBSE Exam Tips
- Remember the purpose of each MySQL data type with suitable examples.
- Learn the difference between CHAR and VARCHAR carefully, as it is frequently asked in examinations.
- Memorize the formats of DATE (YYYY-MM-DD) and TIME (HH:MM:SS).
- Practice choosing the most appropriate data type for real-life database tables.
- Expect competency-based questions where you justify the selection of data types.
Summary
MySQL provides different data types to store different kinds of information efficiently. INT stores whole numbers, FLOAT stores decimal values, CHAR stores fixed-length text, and VARCHAR stores variable-length text. The DATE, TIME, and YEAR data types are used for storing calendar and time-related information. Choosing the appropriate data type improves storage efficiency, ensures data accuracy, and enhances the overall performance of a database.