Examples of DQL Function

Example:

Trunc(column|expression)- truncates the column or the expression to x decimal places.

Example:

Mod(column|expression) - calculates the remainder after dividing the column or expression by x.

Example:

Date functions:

Sysdate:is a function which does not take any argument but returns the current date and time.

Example:

Note:the dual table is owned by the SYS and can be accessed by everybody. It contains only one column and one row. The column heading is DUMMY and the value stored in the column is X.
Month_between( date1, date2) - this function returns the number months between the two months specified.

Example:

Group function or multiple-row functions:

In the above session we knew about single row functions, which operates on a single row to give one result per row. Now we will discuss about multiple-row functions which are also called group functions.
Group functions operate on sets of rows to give one result per group. These sets may be the whole table or the table is split into groups. Let?s see some of the group functions and their uses.

Avg([distinct]column):- average value of column, ignoring null values. We can use avg for numeric data.

Note:-the distinct keyword makes the function consider only non-duplicate values. By default the function includes duplicates.

Count( *|[distinct] column) : the count function calculates the number of rows including duplicate rows with nulls using the distinct keyword with an eliminate the duplicates.

Max([distinct] column): the max function finds the maximum value of the column that you have specified.

Min([distinct] column) :- min function finds the minimum values of the column that you have specified.

Stddev([distinct] column) : stddev function finds the standard deviation of column that you have specified.

Sum([distinct] column): sum function finds the sum of all the values of the column that you have specified.

Example-1:

Example-2: