Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

SQL Server Numeric Functions

Numeric Functions in SQL Server are used to perform mathematical calculations and operations on numeric data.

What are Numeric Functions?

Numeric functions are built-in SQL Server functions that are used to perform calculations and manipulate numerical values.

They are commonly used for rounding numbers, finding absolute values, calculating powers, square roots and performing other mathematical operations.

Example:

If a database contains product prices such as 125.75, 99.49 and -50, numeric functions can be used to round values, find absolute values and perform mathematical calculations.

Common Numeric Functions

Function Purpose Example
ABS() Returns the absolute value of a number. ABS(-25)
CEILING() Returns the smallest integer greater than or equal to the specified number. CEILING(12.4)
FLOOR() Returns the largest integer less than or equal to the specified number. FLOOR(12.8)
ROUND() Rounds a number to the specified number of decimal places. ROUND(125.678, 2)
POWER() Returns a number raised to a specified power. POWER(2, 3)
SQRT() Returns the square root of a number. SQRT(25)
SIGN() Returns the sign of a number. SIGN(-10)
RAND() Returns a pseudo-random floating-point value between 0 and 1. RAND()

1. ABS() Function

The ABS() function returns the absolute value of a number.

An absolute value is always positive or zero.

Syntax
ABS(number)
Example
SELECT ABS(-25);
Output: 25

The negative value -25 becomes 25.

2. CEILING() Function

The CEILING() function returns the smallest integer that is greater than or equal to the specified number.

Syntax
CEILING(number)
Example
SELECT CEILING(12.4);
Output: 13

13 is the smallest integer greater than or equal to 12.4.

3. FLOOR() Function

The FLOOR() function returns the largest integer that is less than or equal to the specified number.

Syntax
FLOOR(number)
Example
SELECT FLOOR(12.8);
Output: 12

4. ROUND() Function

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

Syntax
ROUND(number, decimal_places)
Example
SELECT ROUND(125.678, 2);
Output: 125.68

The number is rounded to two decimal places.

5. POWER() Function

The POWER() function returns a number raised to the specified power.

Syntax
POWER(number, power)
Example
SELECT POWER(2, 3);
Output: 8

Here, 2 is raised to the power of 3: 2 × 2 × 2 = 8.

6. SQRT() Function

The SQRT() function returns the square root of a specified number.

Syntax
SQRT(number)
Example
SELECT SQRT(25);
Output: 5

7. SIGN() Function

The SIGN() function returns the sign of a numeric expression.

  • -1 is returned for a negative number.
  • 0 is returned for zero.
  • 1 is returned for a positive number.
Syntax
SIGN(number)
Example
SELECT SIGN(-10);
Output: -1

8. RAND() Function

The RAND() function returns a pseudo-random floating-point value between 0 and 1.

Syntax
RAND()
Example
SELECT RAND();
Output: A random decimal value between 0 and 1.

Practical Example with a Table

Suppose we have a Products table containing product prices.

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

Insert sample data:

INSERT INTO Products
VALUES
(1, 'Keyboard', 125.75),
(2, 'Mouse', 99.49),
(3, 'Monitor', 450.80);
Round Product Prices
SELECT
    ProductName,
    Price,
    ROUND(Price, 0) AS RoundedPrice
FROM Products;
Find Absolute Value
SELECT
    ABS(-500) AS AbsoluteValue;
Round Price Up
SELECT
    ProductName,
    Price,
    CEILING(Price) AS CeilingPrice
FROM Products;
Round Price Down
SELECT
    ProductName,
    Price,
    FLOOR(Price) AS FloorPrice
FROM Products;

CEILING() vs FLOOR() vs ROUND()

Function Example Result
CEILING() CEILING(12.4) 13
FLOOR() FLOOR(12.8) 12
ROUND() ROUND(12.56, 1) 12.6

When Do We Use Numeric Functions?

  • When mathematical calculations are required.
  • When decimal values need to be rounded.
  • When positive values are required from negative numbers.
  • When calculating powers or square roots.
  • When values need to be rounded up or down.
  • When processing numerical data stored in database tables.

Important Points

  • ABS() returns the absolute value.
  • CEILING() rounds a value upward to an integer.
  • FLOOR() rounds a value downward to an integer.
  • ROUND() rounds a value to the specified decimal places.
  • POWER() is used for exponent calculations.
  • SQRT() returns the square root.
  • SIGN() identifies whether a number is negative, zero or positive.
  • RAND() generates a pseudo-random decimal value.

Summary

SQL Server Numeric Functions are used to perform mathematical operations on numeric data. In this topic, we learned ABS(), CEILING(), FLOOR(), ROUND(), POWER(), SQRT(), SIGN() and RAND(). These functions are useful when working with prices, marks, quantities, calculations and other numerical values stored in database tables.