The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Numeric Functions

Next ►
►

In this section, we will explore various numeric functions available in SQL. Numeric functions are used to perform operations on numeric data types and return numeric values. These functions are essential for data analysis, reporting, and various other database operations.

Key Concepts

  1. Arithmetic Functions: Perform basic arithmetic operations.
  2. Rounding Functions: Round numeric values to a specified precision.
  3. Trigonometric Functions: Perform trigonometric calculations.
  4. Other Numeric Functions: Include functions for absolute values, square roots, and more.

Common Numeric Functions

  1. Arithmetic Functions

  • ABS(number): Returns the absolute value of a number.
  • CEIL(number): Returns the smallest integer greater than or equal to a number.
  • FLOOR(number): Returns the largest integer less than or equal to a number.
  • MOD(number, divisor): Returns the remainder of a number divided by a divisor.
  • POWER(number, exponent): Returns the value of a number raised to the power of an exponent.

  1. Rounding Functions

  • ROUND(number, decimals): Rounds a number to a specified number of decimal places.
  • TRUNC(number, decimals): Truncates a number to a specified number of decimal places.

  1. Trigonometric Functions

  • SIN(number): Returns the sine of a number.
  • COS(number): Returns the cosine of a number.
  • TAN(number): Returns the tangent of a number.

  1. Other Numeric Functions

  • SQRT(number): Returns the square root of a number.
  • EXP(number): Returns e raised to the power of a number.
  • LOG(number): Returns the natural logarithm of a number.
  • LOG10(number): Returns the base-10 logarithm of a number.

Practical Examples

Example 1: Using ABS and MOD

SELECT 
    ABS(-15) AS AbsoluteValue,
    MOD(10, 3) AS Remainder;

Explanation:

  • ABS(-15) returns 15.
  • MOD(10, 3) returns 1.

Example 2: Using CEIL and FLOOR

SELECT 
    CEIL(4.2) AS CeilValue,
    FLOOR(4.8) AS FloorValue;

Explanation:

  • CEIL(4.2) returns 5.
  • FLOOR(4.8) returns 4.

Example 3: Using ROUND and TRUNC

SELECT 
    ROUND(123.456, 2) AS RoundedValue,
    TRUNC(123.456, 2) AS TruncatedValue;

Explanation:

  • ROUND(123.456, 2) returns 123.46.
  • TRUNC(123.456, 2) returns 123.45.

Example 4: Using Trigonometric Functions

SELECT 
    SIN(PI()/2) AS SineValue,
    COS(PI()) AS CosineValue,
    TAN(PI()/4) AS TangentValue;

Explanation:

  • SIN(PI()/2) returns 1.
  • COS(PI()) returns -1.
  • TAN(PI()/4) returns 1.

Example 5: Using SQRT and LOG

SELECT 
    SQRT(16) AS SquareRoot,
    LOG(2.71828) AS NaturalLog,
    LOG10(100) AS Base10Log;

Explanation:

  • SQRT(16) returns 4.
  • LOG(2.71828) returns approximately 1.
  • LOG10(100) returns 2.

Exercises

Exercise 1: Basic Arithmetic Functions

Task: Write a query to calculate the absolute value of -20, the ceiling of 7.3, and the floor of 7.8.

SELECT 
    ABS(-20) AS AbsoluteValue,
    CEIL(7.3) AS CeilValue,
    FLOOR(7.8) AS FloorValue;

Exercise 2: Rounding and Truncating

Task: Write a query to round the number 45.6789 to 2 decimal places and truncate it to 1 decimal place.

SELECT 
    ROUND(45.6789, 2) AS RoundedValue,
    TRUNC(45.6789, 1) AS TruncatedValue;

Exercise 3: Trigonometric Functions

Task: Write a query to find the sine, cosine, and tangent of PI()/3.

SELECT 
    SIN(PI()/3) AS SineValue,
    COS(PI()/3) AS CosineValue,
    TAN(PI()/3) AS TangentValue;

Exercise 4: Square Root and Logarithms

Task: Write a query to find the square root of 25, the natural logarithm of 7.389, and the base-10 logarithm of 1000.

SELECT 
    SQRT(25) AS SquareRoot,
    LOG(7.389) AS NaturalLog,
    LOG10(1000) AS Base10Log;

Summary

In this section, we covered various numeric functions in SQL, including arithmetic, rounding, trigonometric, and other numeric functions. These functions are essential for performing calculations and data analysis in SQL. By practicing the provided examples and exercises, you should now have a solid understanding of how to use these functions effectively.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project

The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved