Tutorial Addendum on SQL - Functions

Numeric Functions

    

"ABS(number)" - Allotment the complete amount of the defined number.

    

"CEILING(number)" - Allotment the aboriginal accumulation amount not beneath than

    

the defined number.

    

"COS(number)" - Allotment the cosine of the defined number.

    

"PI()" - Allotment the amount of PI, 3.141592653589793116.

    

"EXP(number)" - Allotment the amount of e accession to the ability of the defined number.

    

"FLOOR(number)" - Allotment the better accumulation amount not greater than

    

the defined number.

    

"GREATEST(list)" - Allotment the better amount of the defined account of values.

    

"LEAST(list)" - Allotment the aboriginal amount of the defined account of values.

    

"RAND()" - Allotment a accidental amount amid 0 and 1.

    

"ROUND(number)" - Allotment the abutting accumulation of the defined number.

    

"ROUND(number, position)" - Allotment the angled amount of the defined amount

    

at the defined decimal position.

    

"SIGN(number)" - Allotment -1, 0 or 1 if the defined amount is negative, aught or positive.

    

"TRUNCATE(number, position)" - Allotment the truncated amount of the defined amount

    

at the defined decimal position.

    

Examples of numeric functions, NumericFunctions.sql:

    

 

    
-- NumericFunctions.sql

    
-- Absorb (c) 1999 by Dr.  Yang

    
--

    
SELECT PI();

    
SELECT CEILING(PI());

    
SELECT FLOOR(PI());

    
SELECT ROUND(PI(),4);

    
SELECT TRUNCATE(PI(),4);

    

Output:

    

 

    
PI()

    
3.141593

    
CEILING(PI())

    
4

    
FLOOR(PI())

    
3

    
ROUND(PI(),4)

    
3.1416

    
TRUNCATE(PI(),4)

    
3.1415

    

Date and Time Functions

    

"CURRENT_DATE()" - Allotment the accepted date.

    

"CURRENT_TIME()" - Allotment the accepted time.

    

"CURRENT_TIMESTAMP()" - Allotment the accepted date and time.

    

"EXTRACT(field_name FROM date_time)" - Allotment a numeric amount of the specified

    

field name of the defined date and time value.

    

Examples of date and time functions, DateTimeFunctions.sql:

    

 

    
-- DateTimeFunctions.sql

    
-- Absorb (c) 1999 by Dr.  Yang

    
--

    
SELECT CURRENT_TIME();

    
SELECT EXTRACT(HOUR FROM CURRENT_TIME());

    
SELECT EXTRACT(MONTH FROM TIMESTAMP 1999-02-28 10:20:30 );

    

Output:

    

 

    
CURRENT_TIME()

    
21:38:37

    
EXTRACT(HOUR FROM CURRENT_TIME())

    
21

    
EXTRACT(MONTH FROM TIMESTAMP 1999-02-28 10:20:30 )

    
2

    

Cast Function

    

"CAST(value AS type)" - Allotment the casted amount of the defined amount

    

as the defined type.

    

Examples of casting function, CastFunctions.sql:

    

 

    
-- CastFunctions.sql

    
-- Absorb (c) 1999 by Dr.  Yang

    
--

    
SELECT PI() =  3.141593 ;

    
SELECT CAST(PI() AS CHAR) =  3.141593 ;

    
SELECT SUBSTRING(CAST(TIME 10:20:30  AS CHAR),4,2);

    
SELECT CAST(SUBSTRING( JAN 10, 1999 ,5,2) AS SIGNED) + 1;

    

Output:

    

 

    
PI() =  3.141593 

    
0

    
CAST(PI() AS CHAR) =  3.141593 

    
1

    
SUBSTRING(CAST(TIME 10:20:30  AS CHAR),4,2)

    
20

    
CAST(SUBSTRING( JAN 10, 1999 ,5,2) AS SIGNED) + 1

    
11