SQL Data Types

When creating a table in SQL, every column must be assigned a data type. A data type tells the database what kind of value a column will store — whether it is a number, a piece of text, a date, or something else.

Think of it like labeling compartments in a drawer. One compartment is labeled "only pens," another "only coins," and another "only paper." SQL data types work the same way — they make sure only the right kind of data goes into each column.

Why Data Types Matter

They ensure data accuracy — a phone number column will not accidentally store someone's name.
They optimize storage — using the right type helps the database use memory efficiently.
They help with calculations — only numeric types can be used in math operations like addition or average.

Main Categories of SQL Data Types

SQL data types are grouped into three main categories: String (Text), Numeric, and Date and Time.

1. String (Text) Data Types

String data types store text values — like names, addresses, descriptions, and email IDs.

Data Type	Description	Example Value
`CHAR(n)`	Fixed-length text. Always stores exactly n characters. Unused space is filled with blanks.	'Delhi' stored as CHAR(10) = 'Delhi '
`VARCHAR(n)`	Variable-length text. Stores up to n characters. Only uses the space needed.	'Hello' stored as VARCHAR(100) = 'Hello'
`TEXT`	Stores large amounts of text with no fixed limit.	A long product description or article

When to Use CHAR vs VARCHAR

Use CHAR when the value will always be the same length — for example, a country code like 'IN' or 'US'. Use VARCHAR when the length of the value can vary — for example, a person's name or a city name.

-- Example
StudentName VARCHAR(50),
CountryCode CHAR(2)

2. Numeric Data Types

Numeric data types store numbers — both whole numbers and decimal numbers.

Data Type	Description	Example Value
`INT`	Stores whole numbers (no decimals). Range: approximately -2 billion to +2 billion.	25, 1000, -50
`TINYINT`	Stores very small whole numbers. Range: 0 to 255 (unsigned).	1, 50, 200
`BIGINT`	Stores very large whole numbers.	9876543210
`FLOAT`	Stores decimal numbers with approximate precision.	3.14, 99.99
`DECIMAL(p, s)`	Stores exact decimal numbers. p = total digits, s = digits after decimal point.	DECIMAL(8,2) → 99999.99

Example: Using Numeric Types

-- Storing age as INT and price as DECIMAL
Age INT,
Price DECIMAL(8, 2)

A DECIMAL(8, 2) column can store numbers like 12345.99 — up to 8 total digits with exactly 2 after the decimal point. This is commonly used for prices and salaries.

3. Date and Time Data Types

These data types store dates and times — like order dates, birth dates, or appointment times.

Data Type	Description	Format / Example
`DATE`	Stores a calendar date only (no time).	YYYY-MM-DD → '2024-03-15'
`TIME`	Stores a time value only (no date).	HH:MM:SS → '09:30:00'
`DATETIME`	Stores both date and time together.	'2024-03-15 09:30:00'
`TIMESTAMP`	Stores date and time. Often used to track when a record was created or last updated.	'2024-03-15 09:30:00'
`YEAR`	Stores a four-digit year only.	2024

Example: Using Date Types

-- Storing birth date and enrollment date
BirthDate DATE,
EnrollmentDate DATETIME

4. Boolean Data Type

The BOOLEAN (or BOOL) data type stores only two values: TRUE or FALSE. In many databases like MySQL, it is stored internally as 1 (TRUE) or 0 (FALSE).

-- Example
IsActive BOOLEAN

This can be used to track whether a student is currently enrolled, whether an account is active, or whether an order has been delivered.

Complete Table Definition Example

Here is an example showing a table that uses multiple data types together:

CREATE TABLE Students (
    StudentID   INT,
    StudentName VARCHAR(50),
    Age         INT,
    Fees        DECIMAL(8, 2),
    City        CHAR(30),
    JoinDate    DATE,
    IsActive    BOOLEAN
);

What Each Column Stores

Column	Data Type	What It Stores
StudentID	INT	A unique number for each student like 1, 2, 3
StudentName	VARCHAR(50)	Student's name — up to 50 characters
Age	INT	Age as a whole number like 20, 22
Fees	DECIMAL(8,2)	Fee amount like 45000.00
City	CHAR(30)	City name
JoinDate	DATE	Date when student joined, like 2024-01-10
IsActive	BOOLEAN	TRUE if currently studying, FALSE if not

Key Points to Remember

Always choose the smallest data type that fits the data — this saves storage space.
Use VARCHAR instead of CHAR for text that varies in length.
Use DECIMAL instead of FLOAT when exact values matter, like money.
Date values in SQL are always written in YYYY-MM-DD format.
Choosing the wrong data type can cause errors during data insertion or lead to inaccurate query results.

Summary

SQL data types define what kind of data each column in a table can hold. The three main groups are string types (for text), numeric types (for numbers), and date/time types (for dates and times). Choosing the correct data type for each column is a critical step in building a well-structured and efficient database.

Previous lessons

Back to courses

Next lessons