Numeric Data Type SQL

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Overview

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.

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

types of numeric datatypes in sql

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 (1038+1)(-10^{38} + 1) to (10381)(10^{38} - 1). 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:

image of database Refer to the image below to see the kind of numbers allowed in the salary column.

eight digit decimal number 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_numberaccount_namesalary
1Sushant123456.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_idstudent_namedues
1Saumya124

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.

float data types

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_idbus_feedues
1111.0012345.12300
2145.0012345.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:

OperatorDescription
+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:

IDSalaryBonusTax
18600092005000
28500091005500
37300098004500

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.

See Also: