SQL Numeric Value Types

What are SQL Numeric Value Types?

SQL provides various numeric value data types that allow you to store and manipulate numerical data in a structured manner. Numeric data types are used to represent integers, floating-point numbers, and other numerical values. Common SQL numeric data types include INTEGER, NUMERIC, FLOAT, DOUBLE, and more.

When you would use it

You would use SQL numeric data types when you need to:

1. Store numerical data: Store data such as quantities, measurements, prices, and other numeric values.

2. Define column data types: Specify the type of data that can be stored in a column when creating database tables.

3. Control data precision and scale: Specify the precision (total number of digits) and scale (number of decimal places) of numeric data.

4. Perform mathematical calculations: Perform mathematical operations like addition, subtraction, multiplication, and division on numerical data.

5. Sort and compare numbers: Sort and compare numeric data, such as arranging prices in ascending order or comparing numerical values.

Syntax

SQL numeric data types are typically defined when creating database tables. The basic syntax for defining numeric data types is as follows:

column_name data_type (precision, scale);

• column_name: The name of the column where numeric data will be stored.
• data_type: The specific numeric data type (e.g., INTEGER, NUMERIC, FLOAT, DOUBLE) you want to use.
• precision: The total number of digits in the numeric value.
• scale: The number of decimal places in the numeric value.

Parameter values

• column_name: Choose a descriptive name for the column.
• data_type: Select the appropriate numeric data type for your needs. Common SQL numeric data types include:
  • INTEGER: Whole numbers (integers) without decimal places.
  • NUMERIC(precision, scale): Fixed-point numbers with user-defined precision and scale.
  • FLOAT(precision): Approximate floating-point numbers with user-defined precision.
  • DOUBLE PRECISION: Double-precision floating-point numbers with high precision.
• precision: Define the total number of digits in the numeric value.
• scale: Specify the number of decimal places in the numeric value.

Example query

Suppose you want to create a database table called "products" with columns for product IDs and prices. You can define the price column as a fixed-point numeric data type (NUMERIC) with a precision of 10 and a scale of 2 (for two decimal places):

CREATE TABLE products (
    product_id INT,
    price NUMERIC(10, 2)
);

Example table response

The "products" table will be created with columns for product IDs and prices. The "NUMERIC(10, 2)" data type indicates that the "price" column can store fixed-point numbers with up to 10 total digits and 2 decimal places:

| product_id | price |
| ----------- | ----- |
| 1           | 12.99 |
| 2           | 24.50 |
| 3           | 199.00 |

Use cases

• Storing numerical data such as quantities, measurements, prices, and financial data.
• Defining data types for table columns to enforce data integrity.
• Controlling the precision and scale of numeric data.
• Performing mathematical calculations on numeric values.
• Sorting and comparing numeric data.

SQL languages this is available for

SQL numeric data types are fundamental and available in virtually all SQL database systems and implementations, including but not limited to:

• MySQL
• PostgreSQL
• SQL Server
• Oracle Database
• SQLite
• IBM Db2
• MariaDB

The specific data types and their behavior may vary slightly between different database systems, so it's essential to consult the documentation for your specific database system for precise details.