SQL Data Types - Numbers, Strings, Dates, etc.

What Are SQL Data Types?

Every value in a database has a type. Just like in everyday life where names are words and ages are numbers, SQL needs to know what kind of data you're storing—this is where data types come in.

Why Do Data Types Matter?

Data types help define how much space a value needs, what operations are allowed on it, and how it behaves. Assigning the correct type is like assigning the right role in a play—it ensures your data behaves as expected.

Categories of SQL Data Types

SQL data types are broadly classified into:

• Numeric Types: For storing numbers (like marks or age)
• Character/String Types: For text (like names and cities)
• Date and Time Types: For time-sensitive data (like date of birth)
• Boolean: For true/false values (used in conditions)

1. Numeric Data Types

Common Types

• INT: Whole numbers. Ideal for roll numbers or age.
• FLOAT: Numbers with decimals. Useful for percentage or fee amounts.
• DECIMAL(p,s): Fixed-precision decimal. For storing money accurately.

Example

CREATE TABLE student_marks (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  maths_marks FLOAT,
  science_marks DECIMAL(5,2)
);

2. String Data Types

Common Types

• CHAR(n): Fixed-length string (e.g., 10 characters exactly)
• VARCHAR(n): Variable-length string up to 'n' characters
• TEXT: Large blocks of text (not used often for structured tables)

Example

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class CHAR(4),
  city VARCHAR(30)
);

3. Date and Time Data Types

Common Types

• DATE: For date only (YYYY-MM-DD)
• TIME: For time only (HH:MM:SS)
• DATETIME or TIMESTAMP: For date and time

Example

CREATE TABLE attendance (
  roll_no INT,
  date_of_entry DATE,
  entry_time TIME,
  PRIMARY KEY (roll_no, date_of_entry)
);

4. Boolean Type

Although not present in every SQL version (e.g., MySQL handles it as TINYINT), BOOLEAN is used to store TRUE or FALSE values.

Example

CREATE TABLE hostel_allocation (
  roll_no INT,
  has_room BOOLEAN
);

Best Practices When Choosing Data Types

• Keep it specific: Use VARCHAR(30) for city names instead of TEXT.
• Don’t oversize: No need for VARCHAR(255) if names will only be 50 characters max.
• Use DECIMAL for money: Avoid rounding errors that come with FLOAT.

Summary

SQL data types form the foundation of every table. Choosing the right type ensures data integrity, saves space, and improves performance. Whether it's roll numbers or admission dates, give your data the shape it deserves.