In SQL, data types are used to define the type of data that can be stored in a database table’s columns. Some commonly used SQL data types are :
Numeric Data Types
Character Data Types
Date and Time Data Types
Boolean Data Type
Binary Data Types
Numeric Data Types
INTEGER /INT : Used to store whole numbers. Example: Age (28)
DECIMAL(p, s): Used to store decimal numbers with a specified precision (p) and scale (s). Example: Price (12.99, 3.1415)
FLOAT: Used to store approximate numeric values with a floating decimal point. Example: Temperature (98.6, -5.75)
Character Data Types
CHAR(n):
Fixed-length character data type, where n specifies the maximum number of characters.
It allocates a specific amount of storage space, even if the actual data is smaller. It pads the remaining space with spaces.
For example: if you define ‘char(10)’ and store ‘John’ (4 characters), it will use all 10 characters, including 6 spaces.
VARCHAR(n):
Variable-length character data type, where n specifies the maximum number of characters.
It allocates storage space based on the actual data size, without any padding. It is more space-efficient as it only uses the necessary amount of storage.
For example: if you define ‘char(10)’ and store ‘John’ (4 characters), it will use all 5 characters, including 1 information spaces.
NVARCHAR (n)
The ‘nvarchar’ data type is commonly used in databases, particularly in Microsoft SQL Server, to store Unicode character data.
The ‘nvarchar’ data type is similar to ‘varchar’ but is designed to handle Unicode characters, allowing for the storage of multilingual.
TEXT:
Used to store large amounts of character data. Example: Description (…………………….)
Date and Time Data Types
DATE: Used to store dates. Example: Birthdate (‘1990-05-15’)
TIME: Used to store times. Example: Meeting Time (’14:30:00′)
TIMESTAMP: Used to store both date and time information. Example: Last Modified (‘2023-06-25 09:15:30’)
Boolean Data Type
BOOLEAN: Used to store true or false values. Example: Is Active (true, false)
Binary Data Type
BLOB: Used to store large amounts of binary data. Example: Image or File Attachment
Example:
Consider a database table for storing customer information. It may include columns such as:
Create table TBL_CUSTOMER_INFORMATION (
Customer _ID INTEGER PRIMARY KEY,
Name CHAR(50),
Email VARCHAR(100),
Address VARCHAR(100),
Age INTEGER,
Last Purchase DATE,
Active BOOLEAN ,
Customer_card BLOB)
