SQL Data Types
In this lesson, you will learn about the most used types of data in SQL.
What is data type?
A data type is the type of information that can be stored inside a field in the database.
The most important types of data that we will use are the following.
| Data Type | Description |
|---|---|
| INT | Used to store integer numbers. Example: 123 |
| FLOAT | Used to store floating point numbers. Example: 10.5 |
| DOUBLE | Used to store floating point numbers like the type FLOAT, but it can store bigger values. Example: 10.5 |
| DECIMAL | Used to store decimal numbers with a defined format. It's perfect for storing money values because it's guaranteed that the number will be saved the same. Example: If the format is DECIMAL(5,2) then you can store a floating number with a maximum of 5 numbers, but not contain more than 2 numbers after the floating point like 260.25.Notice: if the format is (5,2) and you try to store a number without a floating point like 15 then the database server will store it 15.00 to preserve the format that you specified. |
| BOOLEAN | Used to store logical values; 1 means true and 0 means false.Example: 1 |
| VARCHAR | Used to store text. Example: 'Welcome to FreeSkillAcademy.' |
| DATE | Used to store date with the format YYYY-MM-DD.Example: 2020-01-24 |
There are more data types that can be stored in databases, and their names may differ from one database server to another in addition to their capacity. However, we would like to point out that these differences will not constitute any problem for you in the future when you decide to use a specific type of database. So don't worry at all about other available data types.
When we define data types?
You will deal with data types when you create tables inside the database and define the type of their columns. Also, you will deal with them when you define your functions.
Any information you are going to store later should have a specific data type and it may have some conditions too. for example:
- If you want to store usernames like
"adam", you can specify that the type of the username column isvarchar(30)which means it can contain any text with a maximum of 30 characters. - If you want to store emails like
"adam@example.com", you can specify that the type of the email column isvarchar(100)which means it can contain any text with a maximum of100 characters. - If you want to store birthdates like
"1996-05-16", you can specify that the type of birthday column isdatewhich has the format YYYY-MM-DD by default.
Importance of data types
It is very important to correctly specify the type of information that you want to store in the database because this facilitates dealing with and retrieving them later.
For example, if you store users' salaries in a column with type decimal, then you can retrieve a list of your users sorted ascendingly or descendingly based on their salary. You can also calculate the fees for them easily.
Another example is if you store users' birthdates in a column with type date, then you can retrieve a list of your users sorted ascendingly or descendingly based on their birthdays.
