Home arrow MySQL arrow Page 3 - Advanced Query Writing

Mathematical Functions - MySQL

This article, the first of two parts, covers some advanced topics concerning SQL queries and functions. It is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. Advanced Query Writing
  2. More Character Functions
  3. Mathematical Functions
  4. Date and Time Functions
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 38
March 02, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

As you might guess from the name, mathematical functions return the result of a mathematical operation and usually require a numeric expression as an input parameter, which can be a literal value, a numeric table column value, or any expression (including the output of another function) that yields a numeric value.

SIGN

The SIGN function takes in a numeric expression and returns one of the following values based on the sign of the input number:

Return Value

Meaning

1

Input number is negative

0

Input number is zero

1

Input number is positive

null

Input number is null

Here is an example:

SELECT LATE_OR_LOSS_FEE,
      
SIGN(LATE_OR_LOSS_FEE) AS FEE_SIGN
  
FROM MOVIE_RENTAL
 WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE FEE_SIGN
---------------- --------
           29.99        1
              
4        1
              
4        1
          
29.98        1

SQRT

The SQRT function takes in a single numeric expression and returns its square root. The general syntax is

SQRT (numeric_expression)

The result is a bit meaningless, but letís take the square root of the non-null Late or Loss Fees we just looked at:

SELECT LATE_OR_LOSS_FEE,
       SQRT(LATE_OR_LOSS_FEE) AS FEE_SQRT
 
FROM MOVIE_RENTAL
 
WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE    FEE_SQRT
----------------  ----------
          
29.99  5.47631263
              
4           2
              
4           2
          
29.98  5.47539953

CEILING (CEIL)

The CEILING function returns the smallest integer that is greater than or equal to the value of the numeric expression provided as an input parameter. In other words, it rounds up to the next nearest whole number. There are some interesting naming compatibility issues across SQL implementations: Microsoft SQL Server uses the name CEILING, Oracle uses the name CEIL, and both DB2 and MySQL allow either name (CEIL or CEILING) to be used.

As an example, letís apply CEILING to the Late or Loss Fees (if you are using Oracle, change CEILING to CEIL):

SELECT LATE_OR_LOSS_FEE,
      
CEILING(LATE_OR_LOSS_FEE) AS FEE_CEILING
 
FROM MOVIE_RENTAL
WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE  FEE_CEILING
----------------  -----------
           
4.00            4
           
4.00            4
          
29.99           30
          
29.98           30

FLOOR

The FLOOR function is the logical opposite of the CEILING functionóit returns the integer that is less than or equal to the value of the numeric expression provided as an input parameter. In other words, it rounds down to the next nearest whole number.

Here is an example showing FLOOR applied to Late or Loss Fees:

SELECT LATE_OR_LOSS_FEE,
       FLOOR(LATE_OR_LOSS_FEE) AS FEE_FLOOR
 
FROM MOVIE_RENTAL
 
WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE  FEE_FLOOR
----------------  ---------
            4.00          4
           
4.00          4
          
29.99         29
          
29.98         29



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: