oracle date functions

Date functions

Months_between (’01-sep-95’,’11-jan-94’) =====19.67774194
Add_months(’11-jan-94’,6)------’11-jul-94’
Next_day(’01-sep-95’,’friday’)----’08-sep-95’
Last_day(’01-feb-95’)--------’28-feb-95’



Using date functions

Assume sysdate = ’25-jul-95’;

Round(sysdate,’month’) – 01-aug-95
Round(sysdate,’year’) – 01-jan-96
Trunc(sysdate,’month’) – 01-jul-95
Trunc(sysdate,’year’) – 01-jan-95



YYYY ---- full year in numbers
YEAR ----YEAR SPELLED OUT
MM --- TWO DIGIT VALUE FOR MONTH
MONTH ----FULL NAME OF MONTH
MON -----THRE LETTER ABBREVIATION OF THE MONTH
DY-----THREE LETTER ABBREVIATION OF THE DAY OF THE WEEK
DAY ------FULL NAME OF THE DAY OF THE WEEK
DD- NUMERIC DAY OF THE MONTH


TYPES OF JOINS

EQUIJOIN
Non equijoin
Outer join
Self join
Cross joins
Natural joins
Using clause
Full ot two sided outer joins
Arbitrary join conditions for outer joins

If u want to join n tables together you need a minimum of n-1 conditions

Equijoins:

Equi joins are also called simple join or inner joins

Select * from employees,departments where employye.dept_id = departments.dept_id

Non equi joins:

Select * from employee e,job_grades j where e.salary between j.lowest and j.highest

Outer Joins Syntax:

You use an outer join to also see rows that do not meet the join condition
The outer join operator is the plus sign(+)

Select table1.column,table2.column from table1,table2 where table1.column (+) = table2.column

Self Joins :

Some times you need to join a table to itself . To find the name of each employee’s manager, you need to join the employees table to itself or perform a self join

Creating Cross Joins :

The cross join clause produces the cross product of two tables
This is the same as a Cartesian product between the two tables

Select last_name, department_name from employees,departments;

Natural Joins :

The natural join clause is based on all columns in the two tables that have the same name
It selects rows from the two tables that have equal values in all matched columns
If the columns having the same names have different data types, an error is returned


The natural join can also be written as an equi join



CREATE OR REPLACE TRIGGER ex
AFTER INSERT ON ex2
FOR EACH ROW
DECLARE
ex1 number(10);
ex22 number(10);
BEGIN
ex1 := :NEW.ex1 ;
ex22 := :NEW.ex22 ;
insert into ex3
VALUES(ex1,ex22);
end;


SELECT EX1,
DECODE (LEN(EX2,0),
3,***,
4,****,
5,*****,
6,******,
7,*******,
8,********) HAI
FROM EX4