Learning of SQL Day 14

 

Day 14: Numeric Functions

Introduction:

• Numeric functions in SQL are used to perform mathematical operations on numeric data. These functions are crucial for calculations and data analysis.

Key Concepts:

• ABS(): Returns the absolute value of a number.

• ROUND(): Rounds a number to a specified number of decimal places.

• CEIL() and FLOOR(): Rounds a number up or down to the nearest integer.

• POWER(): Returns a number raised to a specified power.

• SQRT(): Returns the square root of a number.

SQL Commands and Examples:

1. ABS() Function:

   • Get the absolute value of a number.

sql

-- Get the absolute value of -10
SELECT ABS(-10) AS AbsoluteValue;

2. ROUND() Function:

   • Round a number to a specified number of decimal places.

sql

-- Round 123.4567 to 2 decimal places
SELECT ROUND(123.4567, 2) AS RoundedNumber;

3. CEIL() and FLOOR() Functions:

   • Round a number up or down to the nearest integer.

sql

-- Round 123.4567 up to the nearest integer
SELECT CEIL(123.4567) AS RoundedUpNumber;

-- Round 123.4567 down to the nearest integer
SELECT FLOOR(123.4567) AS RoundedDownNumber;

4. POWER() Function:

   • Return a number raised to a specified power.

sql

-- Get 2 raised to the power of 3
SELECT POWER(2, 3) AS PowerResult;

5. SQRT() Function:

   • Return the square root of a number.

sql

-- Get the square root of 16
SELECT SQRT(16) AS SquareRoot;

Practice Exercise:

1. Use the Students table (or any other table with numeric fields).

2. Write a query to get the absolute value of a negative number.

3. Write a query to round a number to 1 decimal place.

4. Write a query to round a number up to the nearest integer.

5. Write a query to round a number down to the nearest integer.

6. Write a query to get 5 raised to the power of 2.

7. Write a query to get the square root of 25.

sql

-- Get the absolute value of -20
SELECT ABS(-20) AS AbsoluteValue;

-- Round 456.789 to 1 decimal place
SELECT ROUND(456.789, 1) AS RoundedNumber;

-- Round 456.789 up to the nearest integer
SELECT CEIL(456.789) AS RoundedUpNumber;

-- Round 456.789 down to the nearest integer
SELECT FLOOR(456.789) AS RoundedDownNumber;

-- Get 5 raised to the power of 2
SELECT POWER(5, 2) AS PowerResult;

-- Get the square root of 25
SELECT SQRT(25) AS SquareRoot;

Mastering numeric functions will enable you to perform various mathematical operations and analyses on your data.