SQL Data Types

Introduction

In this chapter, we will explore the different data types in SQL. Understanding SQL data types is essential because they define the kind of data that can be stored in each column of a table. This chapter will cover various categories of data types and provide examples to help you understand how to use them effectively.

Categories of SQL Data Types

1. Numeric Data Types
2. String Data Types
3. Date and Time Data Types
4. Binary Data Types
5. Miscellaneous Data Types

1. Numeric Data Types

Numeric data types are used to store numerical values. They can be either integers or floating-point numbers.

Common Numeric Data Types

Data Type	Description	Example
INT	Integer	42
FLOAT	Floating-point number	3.14
DECIMAL	Fixed-point number with precision	10.99
SMALLINT	Small integer	123
BIGINT	Large integer	1234567890

Example

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    quantity INT
);

2. String Data Types

String data types are used to store text. They can hold fixed-length or variable-length strings.

Common String Data Types

Data Type	Description	Example
CHAR(n)	Fixed-length string	CHAR(10)
VARCHAR(n)	Variable-length string	VARCHAR(255)
TEXT	Large text data	TEXT

Example

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

3. Date and Time Data Types

Date and time data types are used to store dates, times, or both.

Common Date and Time Data Types

Data Type	Description	Example
DATE	Date (year, month, day)	2023-12-25
TIME	Time (hour, minute, second)	14:30:00
DATETIME	Date and time	2023-12-25 14:30:00
TIMESTAMP	Timestamp	2023-12-25 14:30:00
YEAR	Year	2023

Example

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    delivery_time TIME,
    created_at TIMESTAMP
);

4. Binary Data Types

Binary data types are used to store binary data, such as images or files.

Common Binary Data Types

Data Type	Description	Example
BINARY(n)	Fixed-length binary data	BINARY(16)
VARBINARY(n)	Variable-length binary data	VARBINARY(255)
BLOB	Large binary object	BLOB

Example

CREATE TABLE documents (
    document_id INT PRIMARY KEY,
    document_name VARCHAR(100),
    file_data BLOB
);

5. Miscellaneous Data Types

These are additional data types that do not fit into the other categories.

Common Miscellaneous Data Types

Data Type	Description	Example
BOOLEAN	Boolean value (TRUE or FALSE)	TRUE
ENUM	Enumeration, a list of predefined values	ENUM('small', 'medium', 'large')

Example

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    available BOOLEAN,
    size ENUM('small', 'medium', 'large')
);

Conclusion

Understanding SQL data types is essential for designing effective database schemas. Each data type serves a specific purpose and choosing the right data type ensures data integrity and efficient storage. This chapter covered numeric, string, date and time, binary, and miscellaneous data types. In the next chapter, we will dive into SQL Operators.