Numeric Data Type SQL
Learn via video course
FreeOverview
The numeric data type in SQL is one of the most widely used data types to store numeric-like values. Numbers in SQL can be either exact (NUMERIC, DECIMAL, INTEGER, BIGINT, and SMALL INT) or approximate (DOUBLE PRECISION, FLOAT, and REAL). The integer data type is used to store whole numbers (numbers without the decimal point or simply nondecimal numbers). The integer data type consists of SMALLINT, INTEGER, TINYINT, and BIGINT. The float data type is used to store approximate numeric values.
Scope
This article covers the below topics:
- SQL Numeric Data Type, Numeric-Type Mistakes.
- The Integer Data Types, Integer-Type Mistakes.
- Float Data Types.
- Converting decimal and numeric data.
- Arithmetic operations on numeric data.
Each of the topics is explained clearly with diagrams and examples wherever necessary.
SQL Numeric Data Type
Before learning about numeric data types in SQL, let us first briefly learn about SQL and Database Management Systems. SQL or Structured Query Language is used to manipulate and communicate with the data stored as tables in the database. Before we start manipulating the data, we need to first insert the data into the tables as well. A SQL query (called query in short) is used to fetch the records from tables of a database.
MySQL is a commonly known DBMS that supports a wide range of data types such as char, varchar, binary, text, boolean, bit, integer, float, date, etc. We can use these data types to define the data of our rows and columns. We can broadly categorize these data types into three main categories: string, numeric, and date and time.
The numeric data type in SQL is one of the most widely used data types to store numeric values. Below is the list of data types that are included under the numeric data type in SQL:
- INTEGER or INT
- SMALL INT
- DECIMAL or DEC or FIXED
- NUMERIC
- FLOAT
- REAL
- DOUBLE
- PRECISION
Note: In MySQL, DOUBLE is the synonym for DOUBLE PRECISION. REAL is also a synonym for DOUBLE PRECISION.
Let us learn about the syntax, use cases, examples, and mistakes related to the numeric data type in SQL.
Numbers in SQL can be either exact numbers (like NUMERIC, DECIMAL, INTEGER, BIGINT, and SMALL INT) or approximate numbers (like DOUBLE PRECISION, FLOAT, and REAL). In exact numeric type, precision and scale are preserved.
For example, we can fix the significant digits and precision for a numeric data type. On the other, in approximate numeric types, the precision needs to be preserved, but the scale is not (it can be floating). Refer to the floating and numeric data type section for better understanding.
Syntax:
Here, numeric is a data type that takes 5-17 bytes storage. Precision or p is an integer representing the total number of allowed digits in the specified column. Its default value is 18, and it can be avoided. Allows numbers from to . The radix or base of the precision digit is either decimal (base-10) or binary (base-2). So, the number stored in the specified column can be base-2 or base-10.
Scale or s is also an integer value (s must be a value from 0 top, and its default value is 0) that is used to define the maximum number of decimal places allowed to the right or left of the decimal point. If the number is negative, the left decimal places are considered. Else, the right decimal places are considered.
Let us take the example of a company database to understand the syntax better. Suppose we want to create an employee table with three columns, namely account name, account number, and salary. The salary column is a numeric data type with a scale of 2 (two digits in the right place of the decimal) and a precision of 8 (i.e., the salary can be an eight-digit number).
Suppose we have entered data with the name Sushant, account number as 25, and salary as 51000. The database will look like this:
Refer to the image below to see the kind of numbers allowed in the salary column.
Generally, the numeric data types in SQL have an extra option of UNSIGNED and ZEROFILL. If we add the UNSIGNED option to a column, MySQL will disallow the negative values for that column. On the other hand, if we add the ZEROFILL option to a column, MySQL will automatically add the UNSIGNED attribute to the column.
Numeric-Type Mistakes
There may arise some errors in the numeric column if we try to cross the precision limit. Suppose that the data inserted is too large as per the precision limit, an error is generated. The error may look like this: ERROR 1118 (42000): Row size too large.
Let us take the same employee table example and try inserting values larger than the specified precision limit.
Error is generated in output. Now let us insert data in the precise limit.
No error will be generated because we have inserted the data as per the limits specified during the table creation. Errors are usually generated when we do not consider the constraint conditions of a particular column. The employee table will look like this:
| account_number | account_name | salary |
|---|---|---|
| 1 | Sushant | 123456.33 |
Note: Only two digits after the decimal point are kept in the database because the scale was set to 2.
The Integer Data Types
The integer data type is used to store whole numbers (numbers without the decimal point or simply non-decimal numbers). The numbers can be negative. The integer data type consists of SMALLINT, INTEGER, TINYINT, and BIGINT.
Syntax:
Let us take the example of a college database to understand the syntax better. Suppose we want to create a student table with three columns: student id, student name, and dues. The dues column is an integer column.
Integer-Type Mistakes
Integer data type rounds off the decimal number (for example, 123.5 will get rounded off to 124). This round-off feature of the integer data type may cause inconsistency at some point. If we want exact precision, we can use float data type (Refer to the next section for more detail).
Let us take the example of the same student table and try to insert decimal values in the dues column.
Student table
| student_id | student_name | dues |
|---|---|---|
| 1 | Saumya | 124 |
Float Data Types
The float data type is used to store approximate numeric values just like Integer Data Types are used. The floating data type consists of two parts, namely significant value(M) and exponent value(D). The significant value is a signed numeric value that is the maximum number of digits, the precision.
The exponent value is also a positive value that is used to define the number of digits to the right of the decimal point. For example: if we insert 878.00009 into a FLOAT(7,4) column, the approximate result is 878.0001.
Converting Decimal and Numeric Data
A constant with a decimal point in SQL statements is automatically converted into a numeric data value. The conversion of decimal or numeric value to a floating-point number or float may lead to a loss of precision. On the other hand, the conversion of a floating-point number to a decimal or numeric value may lead to the loss of data (also known as an overflow of data).
By default, the SQL rounds off a decimal or numeric value with lower precision and scale. We can change this round-off scheme, if the SET ARITHABORT option is turned on, an error gets generated in the case of overflow. Loss of only precision and scale isn't sufficient enough to raise an error. Refer to the example below for a better understanding.
Example: Let us take the example of a college database to understand the syntax better. Suppose we want to create a student table with three columns: student id, bus fee, and dues. The dues column is a decimal column, and the bus fee column is a numeric data type.
Let us now insert some values into the student table.
Let us now select the details of the student table and see how conversion automatically works.
Output:
| student_id | bus_fee | dues |
|---|---|---|
| 1 | 111.00 | 12345.12300 |
| 2 | 145.00 | 12345.60000 |
Now, as we can see, the conversion has taken place. The number 12345.123 has been converted to 12345.12300 as we have set the precision and scale to 5.
Arithmetic Operations on Numeric Data
The arithmetic operators are used to perform arithmetic operations on the SQL numeric data types. Arithmetic Operators work with binary operands.
The Arithmetic operators can perform all arithmetic operations like addition, subtraction, multiplication, division, and modulus on the operator's operands.
The SQL Comparison Operators are used with the WHERE clause when we need to provide some conditions regarding the operation.
Following is the list of arithmetic operators present in SQL:
| Operator | Description |
|---|---|
| + | The Addition operator is used to perform additional operations on the operands. |
| - | The Subtraction operator performs subtraction operations on the operands (to get the difference between operands). |
| * | The Multiplication operator performs multiplication operations on the operands. |
| / | The Division operator performs division operations on the operands. |
| % | The Modulus operator is used to perform the modulus operation on the operands (to get the remainder when one operand is divided by the other). |
Let us take an example to understand the Arithmetic operators in a better way.
Suppose we have a database of employees. The employee table has four columns: employee ID, Salary, Tax, and Bonus.
Overview of the employee table:
| ID | Salary | Bonus | Tax |
|---|---|---|---|
| 1 | 86000 | 9200 | 5000 |
| 2 | 85000 | 9100 | 5500 |
| 3 | 73000 | 9800 | 4500 |
Now, let us perform some arithmetic operations using arithmetic operators.
Output:
In this query, we have added the Salary and Bonus amount to calculate the Total Salary of the above table.
Similarly, we can use various other arithmetic operators. If we want to get a tax-free salary.
Output:
Conclusion
- The numeric data type in SQL is one of the most widely used data types to store numeric values.
- Numbers in SQL can be either exact (NUMERIC, DECIMAL, INTEGER, BIGINT, and SMALL INT) or approximate (DOUBLE PRECISION, FLOAT, and REAL).
- The exact numeric type, precision, and scale are preserved. On the other hand, in approximate numeric types, the precision needs to be preserved, but the scale is not (it can be floating).
- The integer data type is used to store whole numbers (numbers without the decimal point or simply nondecimal numbers). The integer data type consists of SMALLINT, INTEGER, TINYINT, and BIGINT.
- The float data type is used to store approximate numeric values. The floating data type consists of two parts, namely significant value(M) and exponent value(D).
- The arithmetic operators perform arithmetic operations on the SQL numeric data types.