DUAL TABLE AND ORACLE FUNCTIONS

Introduction to Oracle Functions

Functions make the result of the query easier and are used to manipulate the data values. Functions can accept any number of constant values or variables. These variables or constant are called as arguments. SQL functions can perform different types of operations such as modify individual data items, convert column data types, format dates and numbers etc.

Categories of Functions
Oracle functions are categorized into two categories:
· Single Row/Scalar Functions
· Group/Aggregate Functions

Functions, which operate on single rows and return one value per row, are called as Scalar functions or Single Row Functions.
On the other hand functions, which operate on set of values to give one result, are called as Group Functions or Aggregate Functions.


Single-Row Functions (Scalar functions)
These functions act on each row of the table and return a single value for each row selected. These functions may accept one or more arguments and can return a value of different data type than that it has accepted.





Classification of Single Row Functions
Single Row Functions can be classified into the following categories:
(i) Character
(ii) Number
(iii) Date
(iv) Conversion
(v) General

Character Functions






Numeric functions
It accept the numeric input and return numeric values.







· round(x[,y])
It rounds off x to the decimal precision of y. If y is negative, rounds to the precision of y places to the left of the decimal point.



Date Functions





Aggregate Functions (Group Functions)


These functions are used to produce summarized results. They are applied on set of rows to give you single value as a result. A group function allows you to perform a data operation on several values in a column of data as though the column was one collective group of data. These functions are called group functions also, because they are often used in a special clause of select statements called a group by clause.

COUNT (x)
This function returns the number of rows or non-null values for column x. When we use * in place of x, it returns the total number of rows in the table.

Syntax:
count([distinctall]column name)
Example:
1. Count the number of employees in the emp table.


SUM(x)

This function returns the sum of values for the column x. This function is applied on columns having numeric datatype and it returns the numeric value.
syntax : sum([distinctall]column name)

Example:

List the total salary paid to the employees in the emp table



AVG(x)

This function returns the average of values for the column x. This function is applied on columns having numeric datatype and it returns the numeric value. It ignores the null values in the column x.

syntax : avg([distinctall]column name)

Example:

List the average salary and the number of employees in the emp table .



MIN(x)

This function returns the minimum of values for the column x for all the rows .this function can be applied on any datatype .
syntax : min([distinctall]column name)

Example:
List the minimum salary in the emp table .

MAX(x)
This function returns the maximum of values for the column x for all the rows .this function can be applied on any datatype.
syntax : max([distinctall]column name)

Example:
List the maximum salary and commission in the emp table .