Numeric Type
Int Type
The INT data type is whole numbers, and they do not have decimal numbers. This is 32-bit signed and has a limit from -2147483648 to 2147483647.
Format
column_name INT
Example
The following is an example of how to create a column using an INT type:
CREATE TABLE cities (
city_id INT,
cityname TEXT,
population INT
);
INSERT INTO cities (city_id, cityname, population)
VALUES
(8557411, 'New York', 8419000),
(8557421, 'London', 8982000),
(8557451, 'Hongkong', 7482000),
(8557491, 'Seoul', 9776000);
Now, run the following query to display the cities table:
SELECT * FROM cities;
It will show the cities table as shown below:
+----------+-----------+------------+
| city_id | cityname | population |
+----------+-----------+------------+
| 8557411 | New York | 8419000 |
| 8557421 | London | 8982000 |
| 8557451 | Hongkong | 7482000 |
| 8557491 | Seoul | 9776000 |
+----------+-----------+------------+
Bigint Type
The BIGINT data type is similar to an Integer but is designed to store numbers exceeding the INT range limit. This is 64-bit signed and has a limit from -9223372036854775808 to 9223372036854775807.
Format
column_name BIGINT
Example
The following is an example of how to create a column using the BIGINT type:
CREATE TABLE galaxies (
galaxy_name TEXT,
star BIGINT
);
INSERT INTO galaxies (galaxy_name, star)
VALUES
('Milky Way', 100000000000),
('Cigar', 30000000000),
('Andromeda', 1000000000000),
('Cosmos', 2000000000000000000);
Now, run the following query to display the table:
SELECT * FROM galaxies;
You will get the galaxy names and their stars as the final output:
+---------------+---------------------+
| galaxy_name | star |
+---------------+---------------------+
| Milky Way | 100000000000 |
| Cigar | 30000000000 |
| Andromeda | 1000000000000 |
| Cosmos | 2000000000000000000 |
+---------------+---------------------+
Real Type
The REAL data type is a 32-bit floating-point number.
Format
column_name REAL
Example
1. Create a Table
Here, we are creating a table named “numbers” with a REAL column type.
CREATE TABLE numbers (
column_1 REAL
);
INSERT into numbers (column_1)
VALUES (1.234568);
Display the table with the following query:
SELECT * FROM numbers;
The following table shows that the column contains at least six decimal digits:
+------------+
| column_1 |
+------------+
| 1.234568 |
+------------+
2. Rounding
Rounding might happen if the precision of an input number is too high.
CREATE TABLE numbers1 (
column_1 REAL
);
INSERT into numbers1 (column_1)
VALUES (1.2345689);
Display the table with the following query:
SELECT * FROM numbers1;
The following table shows that the column can contain at least six decimal digits; the rest is rounded off:
+------------+
| column_1 |
+------------+
| 1.234569 |
+------------+
3. Create a Table With Numbers Exceeding the Range
The REAL type only stores 32-bit floating-point numbers. In this example, we input the numbers that exceed the range:
CREATE TABLE numbers2 (
column_1 REAL
);
INSERT into numbers2 (column_1)
VALUES (1.2345682991822);
Display the table with the following query:
SELECT * FROM numbers2;
The final output will only return numbers that match the range.
+------------+
| column_1 |
+------------+
| 1.2345684 |
+------------+
Double Precision Type
The DOUBLE PRECISION data type is a 64-bit floating-point number.
Format
column_name DOUBLE PRECISION
Example
1. Create a Table
Here, we are creating a table named numbersdouble with a DOUBLE PRECISION type column.
CREATE TABLE numbersdouble (
column_1 DOUBLE PRECISION
);
INSERT into numbersdouble (column_1)
VALUES (1.234568817283122);
Display the table with the following query:
SELECT * FROM numbersdouble;
The following result shows that the column contains sixteen decimal digits, following the **at least **15 decimal digit rule:
+--------------------+
| column_1 |
+--------------------+
| 1.234568817283122 |
+--------------------+
2. Rounding
Rounding might happen if the precision of an input number is too high.
CREATE TABLE numbersdouble1 (
column_1 DOUBLE PRECISION
);
INSERT into numbersdouble1 (column_1)
VALUES (1.234568817283122773);
Display the table with the following query:
SELECT * FROM numbersdouble1;
The following result shows that the column can contain at least fifteen decimal digits:
+---------------------+
| column_1 |
+---------------------+
| 1.2345688172831228 |
+---------------------+