SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)

Mathematical functions are essential tools in SQL, enabling you to perform calculations and transform data directly within queries. Here's a breakdown of the functions you mentioned:

1. SQRT()

  • Description: Returns the square root of a specified number.
  • Example:
    SELECT SQRT(25) AS SquareRoot;  -- Returns 5

2. PI()

  • Description: Returns the value of �� (pi). Note that not all database systems support this function directly.
  • Example:
    -- In some databases like MySQL:
SELECT PI();  -- Returns 3.141592653589793

3. SQUARE()

  • Description: Returns the square of the specified number.
  • Example:
    SELECT SQUARE(5) AS SquareValue;  -- Returns 25

4. ROUND()

  • Description: Rounds a number to the specified number of decimals.
  • Syntax:
    ROUND(number, decimals)
  • Example:
    SELECT ROUND(123.4567, 2) AS RoundedValue;  -- Returns 123.46

5. CEILING()

  • Description: Returns the smallest integer value that is greater than or equal to the specified number.
  • Example:
    SELECT CEILING(123.01) AS CeilingValue;  -- Returns 124

6. FLOOR()

  • Description: Returns the largest integer value that is less than or equal to the specified number.
  • Example:
    SELECT FLOOR(123.99) AS FloorValue;  -- Returns 123

Do note that while the functions listed above are standard across many database systems, there might be variations in implementation or the presence of additional functions in specific RDBMSs. Always consult the official documentation of the database system you are working with to ensure compatibility and understand additional options or nuances.

  1. SQL SQRT function example:

    • The SQRT function is used to calculate the square root of a number.
    SELECT SQRT(25) AS square_root_result;

  2. Using PI in SQL queries:

    • The constant PI() represents the mathematical constant pi (��).
    SELECT PI() AS pi_value;

  3. SQUARE function in SQL:

    • The POWER function can be used to calculate the square of a number.
    SELECT POWER(4, 2) AS square_result;

  4. CEILING vs FLOOR in SQL mathematics:

    • CEILING rounds a number up to the nearest integer, while FLOOR rounds down.
    SELECT CEILING(4.3) AS ceil_result, FLOOR(4.8) AS floor_result;

  5. Mathematical functions in SQL with examples:

    • Various mathematical functions like ABS, EXP, LOG, SIN, COS, and TAN.
    SELECT ABS(-5) AS abs_result,
       EXP(2) AS exp_result,
       LOG(10) AS log_result,
       SIN(30) AS sin_result,
       COS(45) AS cos_result,
       TAN(60) AS tan_result;

  6. Performing calculations with SQL ROUND:

    • The ROUND function is used for rounding a numeric value to a specified number of decimal places.
    SELECT ROUND(4.567, 2) AS rounded_result;

  7. Using SQL CEILING for rounding up:

    • The CEILING function rounds a number up to the nearest integer.
    SELECT CEILING(4.3) AS ceil_result;

  8. Applying FLOOR function in SQL:

    • The FLOOR function rounds a number down to the nearest integer.
    SELECT FLOOR(4.8) AS floor_result;

  9. Calculating square roots in SQL with SQRT:

    • The SQRT function calculates the square root of a number.
    SELECT SQRT(25) AS square_root_result;
Copyright © devanddep.com | All Rights Reserved