Types Of Functions
-------------------------
1) Group Functions / Aggregate Functions
2) Scalar Functions / Single row Functions
1) Group Functions
-------------------------
These functions act on group of rows.
Hence they are called as group functions.
The following are group functions
------------------------------------------
AVG( )
SUM( )
MAX( )
MIN( )
COUNT(*)
COUNT(EXPR)
AVG( ):
---------
Returns average value of the column
SQL> select AVG(sal) from emp;
SUM( ):
----------
Returns sum of valu of the column
SQL> select SUM(sal) from emp;
MAX( ):
---------
Returns Maximum value of the column
SQL> select MAX(sal) from emp;
MIN( ):
---------
Returns minimum value of the column
It ignores numm values
COUNT(*):
--------------
Returns no of rows in the table.
SQL> select COUNT(*) from emp;
COUNT(EXPR):
--------------------
Returns no of values present in the column.
It Ignores null values in the column.
SQL> Select COUNT(COMM) from emp;
2) Scalar Functions:
--------------------------
These functions act on every row of the table
These functions are divided in three types
1) Character Functions
2) Number Functions
3) Date Functions
4) Conversion Functions
1) Character Functions
------------------------------
These functions accepts Character as input.
LOWER( ):
--------------
It converts upper case letters to lower case
SQL> select LOWER('ORACLE') from dual;
SQL> select empno, ename, LOWER(ename) from emp;
UPPER( ):
-------------
It converts lower case letters to upper case
SQL> select UPPER ('oracle') from dual;
INITCAP( ):
---------------
It converts first letter of each word in upper case, keeling all other letters in lower case.
SQL> select INITCAP('free training') from dual;
SQL> select INITCAP(ename) from emp;
CONCAT( ):
---------------
It concatenates two strings
It accepts only two parameters
SQL> select CONCAT ( 'sunil,'karthik') from dual;
SQL> select CONCAT ( ename, job ) from emp;
SUBSTR(value, m, n)
----------------------------
Returns characters from the starting position 'm' to 'n' characters long
It 'n' is less than 1 ot 0 , NULL is returned
It 'n' is omitted , all the characters to the end of the string is returned
SQL> select SUBSTR('oracle' , 2, 3) from dual;
SQL> select SUBSTR('oracle' , 2, 4) from dual;
SQL> select SUBSTR('oracle' , 3, 2) from dual;
SQL> select SUBSTR('oracle' , 4, 0) from dual;
SQL> select SUBSTR('oracle' , 2) from dual;
LENGTH( ):
--------------
Returns no of characters in the value.
SQL> select length('oracle') from dual;
SQL> select * from emp where length(ename) = 4;
Returns position of the character in the string.
Returns first occurance of the character in the string.
Returns 0 when the character does not exist.
2nd parameter can be string.
SQL> select INSTR('oracle', 'a' ) from dual;
SQL> select INSTR('database' ,'a') from dual;
SQL> select INSTR('oracle' ,'h') from dual;
SQL> select INSTR('oracle', 'acl') from dual;
LPAD( string, n, char ):
------------------------------
Pads the char value towards left, to a total width of n character positions.
SQL> select RTRIM ( 'oraclezzzz' , 'z') from dual;
Number Functions:
------------------------
These functions accept numeric input and return numeric values.
1) ABS(n):
--------------
It returns Absolute value of 'n'
SQL> select ABS(-40) from dual;
2) SQRT(n):
----------------
It returns square root of 'n' as real value.
The value 'n' cannot be negative.
SQL> select SQRT(25) from dual;
3) POWER(m,n):
----------------------
Returns 'm' raised to the power of 'n'
SQL> select POWER(2,5) from dual;
4) MOD(m,n):
------------------
It returns remainder of 'm' divided by 'n'
SQL> select mod(11,4), mod(10,2) from dual;
5) FLOOR(n):
------------------
Returns largest integer less than or equal to 'n'
SQL> select FLOOR(40.8) from dual;
SQL> select FLOOR(40.2) from dual;
SQL> select FLOOR(40.5) from dual;
6) CEIL(n):
---------------
Returns smallest integer greater than or equal to 'n'
SQL> select CEIL(40.8) from dual;
SQL> select CEIL(40.2) from dual;
SQL> select CEIL(40.5) from dual;
7) TRUNC(n,m):
---------------------
IF 'm' is omitted, 'n' is truncated to 0 decimal places
It returns 'n' truncated to 'm' decimal places.
SQL> select TRUNC(40.637) from dual;
SQL> select TRUNC(40.637,2) from dual;
8) ROUND(n,m):
----------------------
Rounds off to the nearest integer.
It returns 'n' rounded to 'm' places right to the decimal point.
SQL> select ROUND(40.8) from dual;
SQL> select ROUND(40.2) from dual;
SQL> select ROUND(40.5) from dual;
SQL> select ROUND(40.634,2) from dual;
SQL> select ROUND(40.637,2) from dual;
Working with Dates:
--------------------------
Oracle stores dates in internal numeric format.
Dates can range from 01-january-4712 BC to 31-december-9999 AD
Default display format will be in the form of
DD-MON-YY SYSDATE:
------------------------------
It is a date functions that returns current DATE and TIME.
SQL> select SYSDATE from dual;
It returns number of months between dates D1 and D2
It D1 is later than D2, the result is positive number, else negative.
SQL> select ename , hiredate, SYSDATE, MONTHS_BETWEEN ( SYSDATE, hiredate ) from emp;
3) NEXT_DAY(D, day)
-----------------------------
It returns date of the specified day. That is later than the date 'D'.
day can be full name or the abbreviation.
SQL> select SYSDATE, NEXT_DAY(SYSDATE, 'WED') from dual;
SQL> select SYSDATE, NEXT_DAY('11-jan-81' , 'MONDAY') from dual;
4) LAST_DAY(D):
-----------------------
It returns date of the last day of the month specified.
SQL> select SYSDATE, LAST_DAY(SYSDATE) from dual;
Conversion Functions:
-----------------------------
These functions converts values from one datatype to other.
SQL provides three conversion functions
1) TO_CHAR
2) TO_DATE
3) TO_NUMBER
1) TO_CHAR:
-----------------
This functions has two functionalities.
-> Can convert number to characters
-> Can convert date to characters
Converting number to characters:
--------------------------------------------
SQL> select empno, ename , TO_CHAR(sal, '$9,999') from emp;
Converting number to characters:
--------------------------------------------
SQL> select empno, ename, sal, TO_CHAR(hiredate,'dd-month-yyyy') from emp;