SQL Notes

Hub Advantages and Disadvantages of SQL Features of SQL History of SQL SQL vs NoSQL What is SQL?SQL Comments SQL Data Types SQL Keywords SQL Operators SQL Syntax CHECK Constraint in SQL Composite Keys in SQL DEFAULT Constraint in SQL FOREIGN KEY Constraint in SQL NOT NULL Constraint in SQL PRIMARY KEY Constraint in SQL SQL Constraints UNIQUE Key Constraint in SQL DELETE Data INSERT Data SELECT Data SQL Aliases UPDATE Data BETWEEN Operator DISTINCT Keyword GROUP BY Clause HAVING Clause IN Operator LIKE Operator LIMIT Clause ORDER BY Clause TOP Clause WHERE Clause Wildcard Characters EXCEPT Operator in SQL INTERSECT Operator in SQL MINUS Operator in SQL UNION Operator in SQL UNION ALL Operator in SQL CROSS JOIN in SQL FULL OUTER JOIN INNER JOIN JOIN vs UNION LEFT JOIN RIGHT JOIN SELF JOIN SQL Joins Overview ANY and ALL Operators CASE Expression in SQL Correlated Subquery EXISTS Operator Nested Subqueries in SQL Scalar Subqueries Subquery Introduction Aggregate Functions AVG Function Conversion Functions COUNT Function Date Functions MIN and MAX Functions Numeric Functions String Functions SUM Function ALTER TABLE Create Table DROP TABLE Rename Table Temporary Table TRUNCATE TABLE Backup Database Create Database Drop Database Restore Database CREATE VIEW in SQL DROP VIEW in SQL Materialized Views in SQL Update and Modify Views in SQL Create Stored Procedure in SQL Execute Stored Procedures in SQL Parameters in Stored Procedures Stored Procedure vs Function in SQL Stored Procedure Introduction AFTER Trigger BEFORE Trigger INSTEAD OF Trigger Trigger Examples Trigger Introduction ACID Properties in SQL COMMIT in SQL Isolation Levels in SQL ROLLBACK in SQL SAVEPOINT in SQL Introduction to SQL Transactions Boyce-Codd Normal Form (BCNF)Database Normalization Denormalization First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQL Index Performance Non-Clustered Index Introduction to SQL Indexes Unique Index SQL Execution Plans EXPLAIN Plan Indexing Strategies Partitioning Query Optimization SQL Performance Tips Common Table Expressions (CTE)Dynamic SQL Functions in SQL Materialized Views MERGE Statement PIVOT and UNPIVOT RANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTE Stored Procedures Triggers in SQL Views in SQL Window Functions SQL Challenges SQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL Projects SQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview Questions SQL Scenario-Based Interview Questions Create Your First Database Install MySQL on Your Computer Install PostgreSQL Install SQLite SQL Online Compilers SQL Roadmap

Numeric Functions

Learn SQL Numeric Functions in detail, including mathematical calculations, rounding values, absolute values, power operations, square roots, random numbers, and real-world business applications.

Numbers are at the heart of almost every database application. Businesses constantly work with numerical data such as:

Product prices
Employee salaries
Student marks
Sales revenue
Inventory quantities
Banking transactions
Tax calculations

Simply storing numbers is not enough. Organizations often need to perform calculations, round values, generate reports, calculate percentages, determine profits, and analyze trends.

To make these operations easier, SQL provides Numeric Functions.

Numeric Functions are built-in SQL functions that perform mathematical calculations and transformations on numeric data. These functions help developers and analysts process numbers efficiently without writing complex calculations manually.

They are widely used in finance, accounting, e-commerce, banking, analytics, and reporting systems.

Why are Numeric Functions Important?

Imagine an e-commerce platform.

The database stores:

text example

Product Prices
Discount Percentages
Taxes
Shipping Charges

The system may need to calculate:

text example

Final Product Price
Average Order Value
Tax Amount
Profit Margins

Without Numeric Functions:

text example

Complex Manual Calculations

With SQL:

sql example

SELECT ROUND(Price * 1.18, 2)
FROM Products;

The calculation becomes simple and efficient.

Common SQL Numeric Functions

The most commonly used numeric functions are:

Function	Purpose
ROUND()	Rounds a number
CEIL() / CEILING()	Rounds upward
FLOOR()	Rounds downward
ABS()	Returns absolute value
POWER()	Calculates exponent
SQRT()	Finds square root
MOD()	Returns remainder
RAND()	Generates random numbers
SIGN()	Returns sign of a number
PI()	Returns π value

These functions form the foundation of mathematical operations in SQL.

ROUND() Function

The ROUND() function rounds a number to a specified number of decimal places.

Syntax:

sql example

ROUND(number, decimals)

Example:

sql example

SELECT ROUND(123.4567, 2);

Output:

Output

123.46

Why ROUND() is Useful

Financial systems often require:

text example

Currency Values
Tax Calculations
Interest Calculations

Example:

sql example

SELECT ROUND(1456.789, 2);

Output:

Output

1456.79

This improves readability and accuracy.

CEIL() or CEILING() Function

The CEILING() function rounds a number upward to the nearest integer.

Example:

sql example

SELECT CEILING(12.2);

Output:

Output

Real-World CEILING Example

Suppose:

text example

12.2 Product Boxes Needed

You cannot purchase:

text example

0.2 Box

Therefore:

sql example

SELECT CEILING(12.2);

Output:

Output

FLOOR() Function

The FLOOR() function rounds a number downward.

Example:

sql example

SELECT FLOOR(12.9);

Output:

Output

FLOOR() Use Case

Inventory calculations often use FLOOR() when only complete units can be counted.

Example:

sql example

SELECT FLOOR(25.99);

Output:

Output

ABS() Function

ABS() returns the absolute value of a number.

Syntax:

sql example

ABS(number)

Example:

sql example

SELECT ABS(-500);

Output:

Output

Why ABS() is Useful

In banking systems:

text example

Credit = +5000
Debit = -5000

Using:

sql example

ABS(Amount)

allows the system to focus on transaction magnitude.

POWER() Function

The POWER() function raises a number to a specified exponent.

Syntax:

sql example

POWER(base, exponent)

Example:

sql example

SELECT POWER(2, 3);

Output:

Output

Calculation:

text example

2 × 2 × 2 = 8

POWER() Real-World Example

Compound interest calculations often use POWER().

Example:

sql example

SELECT POWER(1.05, 3);

Calculates three years of 5% growth.

SQRT() Function

The SQRT() function calculates the square root.

Example:

sql example

SELECT SQRT(64);

Output:

Output

Because:

text example

8 × 8 = 64

Why SQRT() is Useful

Applications:

Statistics
Engineering
Scientific calculations
Data analysis

MOD() Function

MOD() returns the remainder after division.

Syntax:

sql example

MOD(number, divisor)

Example:

sql example

SELECT MOD(10, 3);

Output:

Output

Calculation:

text example

10 ÷ 3
= 3 remainder 1

MOD() Real-World Example

Determine even and odd numbers:

sql example

SELECT MOD(15, 2);

Output:

Output

Odd number.

RAND() Function

RAND() generates random numbers.

Example:

sql example

SELECT RAND();

Output:

Output

0.5732

(Random value)

RAND() Use Cases

Used for:

text example

Random Product Selection
Quiz Questions
Lottery Systems
Testing

Example:

sql example

SELECT *
FROM Products
ORDER BY RAND()
LIMIT 1;

Returns a random product.

SIGN() Function

The SIGN() function determines whether a number is:

Output

Positive
Negative
Zero

Example:

sql example

SELECT SIGN(-250);

Output:

Output

-1

Example:

sql example

SELECT SIGN(250);

Output:

Output

Example:

sql example

SELECT SIGN(0);

Output:

Output

PI() Function

Returns the mathematical constant π.

Example:

sql example

SELECT PI();

Output:

Output

3.1415926535

Used in scientific and engineering applications.

Creating a Sample Table

sql example

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

Insert data:

sql example

INSERT INTO Products VALUES
(1, 'Laptop', 59999.99),
(2, 'Mouse', 499.75),
(3, 'Keyboard', 1499.50);

Using ROUND() on Product Prices

Query:

sql example

SELECT
ProductName,
ROUND(Price, 0)
FROM Products;

Result:

Product	Rounded Price
Laptop	60000
Mouse	500
Keyboard	1500

Calculating Tax

Example:

sql example

SELECT
Price,
ROUND(Price * 0.18, 2)
AS Tax
FROM Products;

Useful for GST calculations.

Calculating Discounted Price

Example:

sql example

SELECT
Price,
ROUND(
Price - (Price * 0.10),
2
) AS DiscountedPrice
FROM Products;

Applies a 10% discount.

Real-World Example

E-commerce systems often calculate:

Output

Tax
Discount
Final Price
Shipping Costs

Example:

sql example

SELECT
Price,
ROUND(
(Price * 1.18),
2
)
AS FinalPrice
FROM Products;

Output includes tax-adjusted prices.

Performance Considerations

Numeric Functions are generally efficient.

However:

sql example

ROUND(Column)

on millions of rows may increase processing costs.

Optimization techniques include:

Proper indexing
Query filtering
Pre-calculated values

Common Errors

Using Numeric Functions on Text

Wrong:

sql example

SELECT SQRT(Name)
FROM Employees;

Numeric functions require numeric data.

Division by Zero

Wrong:

sql example

SELECT 10 / 0;

Produces an error.

Always validate input values.

Incorrect Decimal Precision

Example:

sql example

ROUND(123.456, -1)

May produce unexpected results.

Best Practices

Use ROUND() for Financial Reports

Ensures readable currency values.

Validate Numeric Inputs

Prevent invalid calculations.

Avoid Excessive Calculations

Complex formulas on huge datasets may affect performance.

Use Aliases

Example:

sql example

SELECT ROUND(Price, 2)
AS FinalPrice;

Improves readability.

Test Mathematical Logic

Always verify calculations before production deployment.

Common Interview Questions

What is ROUND() used for?

Rounds numbers to a specified decimal place.

What does ABS() return?

The absolute value of a number.

What does CEILING() do?

Rounds a number upward.

What does FLOOR() do?

Rounds a number downward.

What is MOD()?

Returns the remainder after division.

What is POWER()?

Raises a number to a specified exponent.

What is SQRT()?

Calculates the square root of a number.

Summary

Numeric Functions are essential SQL tools used for mathematical calculations, financial processing, analytics, reporting, and business intelligence. They help developers manipulate numeric values efficiently and accurately.

In this lesson, you learned:

What Numeric Functions are
ROUND()
CEILING()
FLOOR()
ABS()
POWER()
SQRT()
MOD()
RAND()
SIGN()
PI()
Real-world examples
Performance considerations
Best practices

Mastering Numeric Functions is important because numerical calculations are a core part of nearly every database-driven application.

Next Step

Continue to the next lesson:

Conversion Functions →

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Numeric Functions.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, functions, numeric, numeric functions

Continue learning this concept

FunctionsAggregate FunctionsLearn SQL Aggregate Functions including COUNT(), SUM(), AVG(), MIN(), and MAX() to perform calculations and summarize data efficiently.FunctionsAVG FunctionLearn the SQL AVG() function in detail, understand how averages are calculated, work with GROUP BY, handle NULL values, and generate meaningful business insights.FunctionsConversion FunctionsLearn SQL Conversion Functions in detail, including CAST(), CONVERT(), implicit conversion, explicit conversion, data type transformations, formatting, and real-world examples.FunctionsCOUNT FunctionLearn the SQL COUNT() function in detail, including syntax, counting rows, counting specific columns, counting unique values, NULL handling, performance considerations, practical examples, and interview questions.FunctionsDate FunctionsLearn SQL Date Functions in detail, understand how to work with dates and times, perform calculations, extract date parts, and build real-world reporting queries.FunctionsMIN and MAX FunctionsMaster SQL MIN() and MAX() aggregate functions. Learn syntax, real-world examples, finding extremes in datasets, handling NULL values, GROUP BY integration, performance optimization, and essential interview questions.

PreviousMIN and MAX Functions NextString Functions

SQL Notes

Numeric Functions

Learn SQL Numeric Functions in detail, including mathematical calculations, rounding values, absolute values, power operations, square roots, random numbers, and real-world business applications.

Numbers are at the heart of almost every database application. Businesses constantly work with numerical data such as:

Product prices
Employee salaries
Student marks
Sales revenue
Inventory quantities
Banking transactions
Tax calculations

Simply storing numbers is not enough. Organizations often need to perform calculations, round values, generate reports, calculate percentages, determine profits, and analyze trends.

To make these operations easier, SQL provides Numeric Functions.

They are widely used in finance, accounting, e-commerce, banking, analytics, and reporting systems.

Why are Numeric Functions Important?

Imagine an e-commerce platform.

The database stores:

text example

Product Prices
Discount Percentages
Taxes
Shipping Charges

The system may need to calculate:

text example

Final Product Price
Average Order Value
Tax Amount
Profit Margins

Without Numeric Functions:

text example

Complex Manual Calculations

With SQL:

sql example

SELECT ROUND(Price * 1.18, 2)
FROM Products;

The calculation becomes simple and efficient.

Common SQL Numeric Functions

The most commonly used numeric functions are:

Function	Purpose
ROUND()	Rounds a number
CEIL() / CEILING()	Rounds upward
FLOOR()	Rounds downward
ABS()	Returns absolute value
POWER()	Calculates exponent
SQRT()	Finds square root
MOD()	Returns remainder
RAND()	Generates random numbers
SIGN()	Returns sign of a number
PI()	Returns π value

These functions form the foundation of mathematical operations in SQL.

ROUND() Function

The ROUND() function rounds a number to a specified number of decimal places.

Syntax:

sql example

ROUND(number, decimals)

Example:

sql example

SELECT ROUND(123.4567, 2);

Output:

Output

123.46

Why ROUND() is Useful

Financial systems often require:

text example

Currency Values
Tax Calculations
Interest Calculations

Example:

sql example

SELECT ROUND(1456.789, 2);

Output:

Output

1456.79

This improves readability and accuracy.

CEIL() or CEILING() Function

The CEILING() function rounds a number upward to the nearest integer.

Example:

sql example

SELECT CEILING(12.2);

Output:

Output

Real-World CEILING Example

Suppose:

text example

12.2 Product Boxes Needed

You cannot purchase:

text example

0.2 Box

Therefore:

sql example

SELECT CEILING(12.2);

Output:

Output

FLOOR() Function

The FLOOR() function rounds a number downward.

Example:

sql example

SELECT FLOOR(12.9);

Output:

Output

FLOOR() Use Case

Inventory calculations often use FLOOR() when only complete units can be counted.

Example:

sql example

SELECT FLOOR(25.99);

Output:

Output

ABS() Function

ABS() returns the absolute value of a number.

Syntax:

sql example

ABS(number)

Example:

sql example

SELECT ABS(-500);

Output:

Output

Why ABS() is Useful

In banking systems:

text example

Credit = +5000
Debit = -5000

Using:

sql example

ABS(Amount)

allows the system to focus on transaction magnitude.

POWER() Function

The POWER() function raises a number to a specified exponent.

Syntax:

sql example

POWER(base, exponent)

Example:

sql example

SELECT POWER(2, 3);

Output:

Output

Calculation:

text example

2 × 2 × 2 = 8

POWER() Real-World Example

Compound interest calculations often use POWER().

Example:

sql example

SELECT POWER(1.05, 3);

Calculates three years of 5% growth.

SQRT() Function

The SQRT() function calculates the square root.

Example:

sql example

SELECT SQRT(64);

Output:

Output

Because:

text example

8 × 8 = 64

Why SQRT() is Useful

Applications:

Statistics
Engineering
Scientific calculations
Data analysis

MOD() Function

MOD() returns the remainder after division.

Syntax:

sql example

MOD(number, divisor)

Example:

sql example

SELECT MOD(10, 3);

Output:

Output

Calculation:

text example

10 ÷ 3
= 3 remainder 1

MOD() Real-World Example

Determine even and odd numbers:

sql example

SELECT MOD(15, 2);

Output:

Output

Odd number.

RAND() Function

RAND() generates random numbers.

Example:

sql example

SELECT RAND();

Output:

Output

0.5732

(Random value)

RAND() Use Cases

Used for:

text example

Random Product Selection
Quiz Questions
Lottery Systems
Testing

Example:

sql example

SELECT *
FROM Products
ORDER BY RAND()
LIMIT 1;

Returns a random product.

SIGN() Function

The SIGN() function determines whether a number is:

Output

Positive
Negative
Zero

Example:

sql example

SELECT SIGN(-250);

Output:

Output

-1

Example:

sql example

SELECT SIGN(250);

Output:

Output

Example:

sql example

SELECT SIGN(0);

Output:

Output

PI() Function

Returns the mathematical constant π.

Example:

sql example

SELECT PI();

Output:

Output

3.1415926535

Used in scientific and engineering applications.

Creating a Sample Table

sql example

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

Insert data:

sql example

INSERT INTO Products VALUES
(1, 'Laptop', 59999.99),
(2, 'Mouse', 499.75),
(3, 'Keyboard', 1499.50);

Using ROUND() on Product Prices

Query:

sql example

SELECT
ProductName,
ROUND(Price, 0)
FROM Products;

Result:

Product	Rounded Price
Laptop	60000
Mouse	500
Keyboard	1500

Calculating Tax

Example:

sql example

SELECT
Price,
ROUND(Price * 0.18, 2)
AS Tax
FROM Products;

Useful for GST calculations.

Calculating Discounted Price

Example:

sql example

SELECT
Price,
ROUND(
Price - (Price * 0.10),
2
) AS DiscountedPrice
FROM Products;

Applies a 10% discount.

Real-World Example

E-commerce systems often calculate:

Output

Tax
Discount
Final Price
Shipping Costs

Example:

sql example

SELECT
Price,
ROUND(
(Price * 1.18),
2
)
AS FinalPrice
FROM Products;

Output includes tax-adjusted prices.

Performance Considerations

Numeric Functions are generally efficient.

However:

sql example

ROUND(Column)

on millions of rows may increase processing costs.

Optimization techniques include:

Proper indexing
Query filtering
Pre-calculated values

Common Errors

Using Numeric Functions on Text

Wrong:

sql example

SELECT SQRT(Name)
FROM Employees;

Numeric functions require numeric data.

Division by Zero

Wrong:

sql example

SELECT 10 / 0;

Produces an error.

Always validate input values.

Incorrect Decimal Precision

Example:

sql example

ROUND(123.456, -1)

May produce unexpected results.

Best Practices

Use ROUND() for Financial Reports

Ensures readable currency values.

Validate Numeric Inputs

Prevent invalid calculations.

Avoid Excessive Calculations

Complex formulas on huge datasets may affect performance.

Use Aliases

Example:

sql example

SELECT ROUND(Price, 2)
AS FinalPrice;

Improves readability.

Test Mathematical Logic

Always verify calculations before production deployment.

Common Interview Questions

What is ROUND() used for?

Rounds numbers to a specified decimal place.

What does ABS() return?

The absolute value of a number.

What does CEILING() do?

Rounds a number upward.

What does FLOOR() do?

Rounds a number downward.

What is MOD()?

Returns the remainder after division.

What is POWER()?

Raises a number to a specified exponent.

What is SQRT()?

Calculates the square root of a number.

Summary

In this lesson, you learned:

What Numeric Functions are
ROUND()
CEILING()
FLOOR()
ABS()
POWER()
SQRT()
MOD()
RAND()
SIGN()
PI()
Real-world examples
Performance considerations
Best practices

Mastering Numeric Functions is important because numerical calculations are a core part of nearly every database-driven application.

Next Step

Continue to the next lesson:

Conversion Functions →

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Numeric Functions.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, functions, numeric, numeric functions

Continue learning this concept

PreviousMIN and MAX Functions NextString Functions