ORACLE FUNCTIONS

ORACLE FUNCTIONS

Oracle functions serve the purpose of manipulating data items and returning a result. Functions are also capable of accepting user-supplied variables or constants and operating on them. Such variables or constants are called arguments.

Like function_name (arguments1, arguments2,…..)

Oracle Function can be clubbed together depending upon whether they operate on a single row or a group rows retrieved from a table. Accordingly, functions can be classified as follows:

Group Functions (aggregate function)

Functions that act on a set of values are called set of values are called Group Functions. For example, SUM, is a function, which calculates the total set of numbers. A group function returns a single row for a group of queries rows.



Scalar Functions (Single Row Functions)
Functions that act on only one value at a time are called Scalar Functions. For example, LENGTH, is a function, which calculates the length of one particular string value. A single row function returns one result for every row of a queried table or view.

Aggregate function

Select max(sal) from emp ;
Select min(sal) from emp ;
Select avg(sal) from emp ;
select count(*) from emp ;
Select sum(sal) from emp ;

Numeric Functions

select abs(-15) from dual ;
select power(3,2) from dual ;
select sqrt(25) from dual ;
select round(10.4567,2) from dual ;
select trunc(10.4567,2) from dual ;

String Functions

select length('SURESH') from dual ;
select lower('Sql Cousre') from dual ;
select lower(ename) from emp ;
select upper(ename) from emp ;
select initcap(ename) from emp ;
select concat('Hello','World') from dual ;
select concat(empno,ename) from emp ;
select substr(ename,1,3) from emp;
select instr('helloworld','w') from dual ;
select ename,instr(ename,'A') from emp ;
select lpad(sal,10,'*') from emp ;
select rpad(sal,10,'*') from emp ;

select ascii('s'),ascii('S') from dual ;
select ename from emp where rowid=CHARTOROWID('AAAH2CAABAAAPAAAAK') ;
select chr(67) from dual;
select chr(67)||chr(65)||CHR(84) from dual;
select dump('abc',1016) from dual ;
select greatest('ASSASA','DDD','CCCC') from dual ;


Date Functions

select empno,hiredate,months_between(sysdate,hiredate) from emp ;
select empno,hiredate,add_months(hiredate,3) from emp ;
select empno,hiredate,next_day(hiredate,'FRIDAY') from emp ;
select empno,hiredate,last_day(hiredate) from emp ;
select empno,hiredate,months_between(sysdate,hiredate),add_months(hiredate,3),
next_day(hiredate,'FRIDAY'), last_day(hiredate) from emp ;

USING CASE Expression

select ename,mgr,
case job when 'SALESMAN' then 2*sal
when 'CLERK' then 3*sal
when 'PRESIDENT' then 4*sal
when 'MANAGER' then 5*sal
else sal*3
end "Revised Salary"
from emp ;

USING DECODE Function

select ename,mgr,sal, decode (job, 'SALESMAN' , 2*sal,
'CLERK' , 3*sal,
'PRESIDENT' , 4*sal,
'MANAGER', 5*sal, sal)
"Revised Salary"
from emp;

Conversion Functions

TO_DATE: Converts character field to a date type
TO_CHAR: date type to character type
TO_CHAR: number type to character
TO_NUMBER: Converts character to a number data type

select to_date('01/01/2005','mm/dd/yyyy') from dual ;

select to_date('23-05-2005','dd-mm-yyyy') from dual ;
select to_char(sysdate,'dd/mm/yyyy') from dual ;
SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual ;
select to_number('3456') +1 from dual ;
select to_number('3456') +1 from dual ;

Group By Clause

The Group By clause is used with SELECT to combine a group of rows based on the values of a particular column or expression. Aggregate functions rea used to return summary information for each group. The aggregate functions are applied to the individual groups.

- List the department no and number of employees in each department no
SQL> select deptno,count(*) from emp group by deptno ;

- List the department no and maxumim salary of employees in each department no
select deptno,max(sal) from emp group by deptno ;
select deptno,job, max(sal) from emp group by deptno,job ;
select deptno,job, max(sal) from emp group by deptno,job ;
select deptno,job, max(sal) from emp group by deptno,job order by deptno,job ;

select deptno,job,sum(sal) from emp group by rollup(deptno,job) ;
select deptno,job,sum(sal) from emp group by cube(deptno,job) ;
select empno,ename,job,sal,deptno, rank() over (partition by deptno, job order by sal desc) "R" from emp;

select lower(ename),upper(ename) from emp ;
select upper(rtrim(ename)) + ' working as a ' + upper(rtrim(job)) from emp ;
select user, uid from dual;

level Command-->
select level, empno, ename, mgr from emp start with mgr is null
connect by prior empno=mgr Order by level;


The Having Clause
HAVING clause is used to specify which group are to be displayed, that is, restrict the groups that you return on the basis of aggregate functions.

- List the average salary for all department employing more than five people.

select deptno, max(sal) from emp group by deptno having count(*)> 5

Note: The WHERE clause cannot be used to restrict the groups that are returned. We can only use where to restrict individual rows.

-- List the total salary, maximum and minimum salary and the avrage salary of emplyees job wise department no 20 and sipaly only those rows having average salary greater than 1000. The output should be arranged in the descending order of sum(sal).

SQL> select job,sum(sal), min(sal), Max(sal), Avg(sal) from emp where deptno=20 group by job having avg(sal) > 1000 order by sum(sal);

Order By Clause

ORDER BY Clause will arrange the final result in the order specified

select empno,ename,sal from emp order by ename asc ;
select empno,ename,sal from emp order by ename desc;
select empno,ename,sal from emp order by empno asc, ename desc;
select empno,ename,sal from emp order by 3,2,1 ;

Integrity Constraints

• Constraints are enforced on data being stored in a table, are called Constraints.
• Constraints super control the data being entered into a table for permanent storage.
• Constraints are preferred Rules applied on thable columns while creating or after creation
• These are automatically activated whenever DML statement is performed on a table
• Provies high security

Integrity Constraints are three types
1.Entity Integraty: - check for uniqueness of data
ex- primary key, Unique
2. Domain Constraint : - Check for conditional rules
ex:- Check, Not null
3. Referential Constraint : Used bind relation between tables

NOT NULL: Prevent a column from accepting NULL values
UNIQUE : Ensures uniqueness of the values in a column
PRIMARY KEY : Same as UNIQUE, but only one column per table is allowed
CHECK: Controls the value of a column(s) being inserted
DEFAULT: Assigns a default value for the column(s), at the time of insertion when no value is given for that column
REFERENCES: Assigns a Foreign Key constraint to maintain “Referential Integrity”
ON DELETE CASCADE: Using this option whenever a parent row is deleted then all the corresponding child rows are deleted from the details Tbale. This option is always used with foreing key.

Example
Create a table using Primary Key and Not null constraints

create table employee
(empno number(4),
ename varchar2(30) not null,
Job varchar2(30) unique ,
deptno number(2) not null,
constraint Emp_pk_id primary key (empno)) ;

- For foreign key , create the master table first and after that child table.

create table department
(deptno number(2) primary key ,
dname varchar2(30) not null,
location varchar2(30)) ;

- Creating a table using foreign key, primary key, unique , check and default constraint

create table employee
(empno number(4) primary key,
ename varchar2(30) not null,
Job varchar2(30) unique ,
sal number(5,2) check (sal > 0) ,
DOJ date default sysdate,
deptno number(2) ,
constraint EmpNO_fk foreign key (deptno) references department(deptno) on delete cascade) ;


Note: If a user is not giving the constraint name, Oracle automatically create a constraint named as “SYS_C0 (some number)”

Dropping the Primary key constraint

SQL> alter table employee drop primary key ;
or
alter table employee drop constraint constraintname;
alter table employee add constraint Dept_pk primary key (deptno) ;

To see details about the infomration of constraints

SQL> desc user_constraints
SQL> select constraint_name,constraint)type from user_constraints where table_Name=’TABLENAME’ ;