SQL Functions

Oracle has many built-in function which help us to perform complex  operations easily and quickly. We can see  built-in function by running below SQL.

SELECT DISTINCT object_name, package_name 
FROM all_arguments
WHERE package_name = 'STANDARD';

SQL Function Type:

• Single Row Functions(or scalar functions):  These functions return a single result for every row of a query table or view(this functions appear in SELECT,WHERE and ORDER BY).

• Group Functions: These functions manipulate groups of rows and return one result per group of rows(this functions appear in SELECT and HAVING clauses).

Use of DUAL table in Oracle

This is a single column and single row dummy table provided by oracle. This is used to perform mathematical calculations without using a table.

Single Row Functions are

1. Character Functions:  Input as character and out put as both character or number values.
2. Numeric Functions:  Input as numeric and output as numeric values.
3. Date Functions:  Input as date data type and out put as both date or number.
4. Conversion Functions: Convert a value from one data type to another data type in run time.

Group Function or Aggregate Function:

• Group Function return a single row based on groups of rows
• Group Function operate on sets(the whole table or the table split into groups) of rows to give one result per group.
• Group Function can appear in SELECT and HAVING clause.

Group Functions Are:

1. AVG 
2. SUM 
3. MAX 
4. MIN 
5. COUNT 
6. STDDEV 
7. VARIANCE 

Use Of Group Function:

• All group function except COUNT ignore NULL values.
• Group function declare in SELECT list and other columns can be declare but other columns(non functional columns) should be in GROUP BY clause.

More Practice      SQL Function Questions