SQL data types tutorial with examples for beginners

SQL Data Types – SQL Tutorial for Beginners

SQL Data Types define the type of data a column can store inside a database table. Choosing the correct data type is one of the most important steps in database design because it affects:

• Storage space

• Query performance

• Data accuracy

• Validation and integrity

In this beginner-friendly SQL tutorial, you’ll learn:

• What SQL data types are

• Common data types in SQL (string, numeric, date/time, etc.)

• MySQL-specific data types

• Best practices for selecting the right data type

• Real-world examples

---

🔹 What Are SQL Data Types?

Every column in a table must be assigned a data type. A data type tells the database:

• What kind of data the column will store

• How much space it will consume

• How it behaves in queries

Example:

CREATE TABLE users (
  id INT,
  name VARCHAR(100),
  created_at DATETIME
);

Here:

• INT → stores numbers

• VARCHAR(100) → stores text up to 100 characters

• DATETIME → stores date and time values

---

🔹 Categories of SQL Data Types

SQL data types fall under these main categories:

1. String / Character Types

2. Numeric Types (Integer & Decimal)

3. Date & Time Types

4. Boolean Type

5. Binary Types

6. JSON & Special Types (DB-specific)

Let's explore each with examples.

---

🔸 1. String / Character Data Types

Used to store text values.

Data Type	Description	Example
CHAR(n)	Fixed-length string	CHAR(10)
VARCHAR(n)	Variable-length string	VARCHAR(255)
TEXT	Long text	Article content
ENUM	Predefined values	ENUM('Male','Female')

Example:

name VARCHAR(100),
city CHAR(20),
bio TEXT

---

🔸 2. Numeric Data Types

Used to store integer and decimal values.

Integer Types

Type	Range
TINYINT	-128 to 127
INT	-2147483648 to 2147483647
BIGINT	Very large numbers

Decimal Types

Type	Description
DECIMAL(a,b)	Exact fixed-point numbers
FLOAT	Approximate floating numbers
DOUBLE	Double-precision floating numbers

Example:

age INT,
salary DECIMAL(10,2),
rating FLOAT(4,2)

---

🔸 3. Date & Time Data Types

Used to store date, time, or both.

Type	Description
DATE	YYYY-MM-DD
TIME	HH:MM:SS
DATETIME	Date & time
TIMESTAMP	Stores Unix timestamp (auto updates)

Example:

created_at DATETIME,
login_time TIMESTAMP

---

🔸 4. Boolean Data Type

Most SQL databases store boolean values as:

BOOLEAN or TINYINT(1)

Example:

is_active BOOLEAN

---

🔸 5. Binary Data Types

Used for storing files and encrypted data.

Type	Description
BLOB	Binary Large Object
BINARY	Fixed-length binary
VARBINARY	Variable-length binary

Example:

profile_image BLOB

---

🔸 6. JSON & Special Types (Optional)

Modern databases like MySQL, PostgreSQL, and SQL Server support JSON.

preferences JSON

This allows storing structured key-value data.

---

🔸 MySQL-Specific Data Types

MySQL includes unique types such as:

✔ ENUM – Predefined set of values

gender ENUM('Male','Female','Other')

✔ SET – Multiple values from a list

hobbies SET('Reading','Gaming','Traveling')

---

🔹 Choosing the Right Data Type (Best Practices)

✔ Use the smallest possible type

If age fits in TINYINT, don’t use INT.

✔ Use VARCHAR instead of TEXT when possible

Faster and more index-friendly.

✔ Use DECIMAL for money values

Avoid floating-point rounding issues.

✔ Use TIMESTAMP for record updates

Useful for automatic CURRENT_TIMESTAMP values.

✔ Use ENUM only when the value set is fixed & small

Easy validation and storage.

---

🔹 Example: Table Using Proper Data Types

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(150),
  age TINYINT,
  salary DECIMAL(10,2),
  join_date DATE,
  is_active BOOLEAN,
  profile BLOB
);

---

Summary

In this SQL Data Types tutorial, you learned:

• What SQL data types are

• Different categories of data types

• MySQL-specific data types

• Best practices for choosing the right type

• Real-world examples

Choosing the correct data type improves performance, saves storage, and ensures data accuracy in your database.