Data Types in SQL

Introduction

In the world of databases, understanding data types is crucial. SQL (Structured Query Language) provides a variety of data types to store different kinds of information effectively. Whether you're just starting out or looking to deepen your knowledge, this tutorial will guide you through the basics and some intermediate concepts of SQL data types.

Concept

Data types in SQL define the type of data that can be stored in a column within a table. They help ensure data integrity and optimize storage and retrieval processes. Here are some common data types categorized into groups:

1. Numeric Types

• INTEGER: Stores whole numbers without a fractional component.
• DECIMAL(p, s): Stores fixed-point numbers with precision p and scale s.
• FLOAT: Stores floating-point numbers.

2. Character Types

• CHAR(n): Fixed-length character string of length n.
• VARCHAR(n): Variable-length character string up to n characters.
• TEXT: Variable-length character string with no fixed limit.

3. Date and Time Types

• DATE: Stores date values without time information.
• TIME: Stores time values without date information.
• DATETIME: Stores both date and time values.

4. Boolean Type

• BOOLEAN: Stores true or false values.

Examples

Let's explore these data types with practical examples using SQL commands.

Numeric Types

INTEGER

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(100),
    Age INT
);

INSERT INTO Employees (EmployeeID, Name, Age) VALUES (1, 'John Doe', 30);

DECIMAL

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Laptop', 999.99);

FLOAT

CREATE TABLE Measurements (
    MeasurementID INT,
    Value FLOAT
);

INSERT INTO Measurements (MeasurementID, Value) VALUES (1, 3.14159);

Character Types

CHAR and VARCHAR

CREATE TABLE Customers (
    CustomerID INT,
    FirstName CHAR(20),
    LastName VARCHAR(50)
);

INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (1, 'Jane', 'Doe');

TEXT

CREATE TABLE Articles (
    ArticleID INT,
    Title VARCHAR(100),
    Content TEXT
);

INSERT INTO Articles (ArticleID, Title, Content) VALUES (1, 'SQL Basics', 'This article covers the basics of SQL...');

Date and Time Types

DATE

CREATE TABLE Events (
    EventID INT,
    EventName VARCHAR(100),
    EventDate DATE
);

INSERT INTO Events (EventID, EventName, EventDate) VALUES (1, 'Conference', '2023-10-05');

TIME

CREATE TABLE Timers (
    TimerID INT,
    StartTime TIME,
    EndTime TIME
);

INSERT INTO Timers (TimerID, StartTime, EndTime) VALUES (1, '09:00:00', '17:00:00');

DATETIME

CREATE TABLE Logs (
    LogID INT,
    Message VARCHAR(255),
    Timestamp DATETIME
);

INSERT INTO Logs (LogID, Message, Timestamp) VALUES (1, 'System started', '2023-10-01 14:23:00');

Boolean Type

CREATE TABLE Preferences (
    PreferenceID INT,
    Feature VARCHAR(50),
    IsActive BOOLEAN
);

INSERT INTO Preferences (PreferenceID, Feature, IsActive) VALUES (1, 'Dark Mode', TRUE);

What's Next?

Now that you have a good understanding of SQL data types, the next step is to learn how to create databases. This will allow you to apply what you've learned and start building your own database systems.

Stay tuned for more tutorials on SQL and other programming topics!