Find Number of years between 2 dates DAtastage

I understand MONTHS_BETWEEN database function will work. 

How to start Application Express

After installing Oracle Express, point your web browser to http://localhost:8080/apex
To change the default port:

sqlplus /nolog
connect sys/mypassword as sysdba
exec  dbms_xdb.sethttpport('9090');

Oracle How to pivot a table

create table winners
(year varchar(4) not null,
team varchar(30) not null,
constraint pk_winners primary key (year));

insert into winners (year,team) values ('1990','SAINTS');
insert into winners (year,team) values ('1991','SWANS');
insert into winners (year,team) values ('1992','DOGS');
insert into winners (year,team) values ('1993','MAGPIES');
insert into winners (year,team) values ('1994','TIGERS');


SQL> select * from winners;

YEAR TEAM
---- ------------------------------
1990 SAINTS
1991 SWANS
1992 DOGS
1993 MAGPIES
1994 TIGERS

5 rows selected.

SQL> select max(decode(year,'1990',team,'')) as "1990",
max(decode(year,'1991',team,'')) as "1991",
max(decode(year,'1992',team,'')) as "1992",
max(decode(year,'1993',team,'')) as "1993",
max(decode(year,'1994',team,'')) as "1994"
from winners

1990       1991       1992       1993       1994
---------- ---------- ---------- ---------- ----------
SAINTS     SWANS      DOGS       MAGPIES    TIGERS

1 row selected.

Oracle:How to get first and last rows in an aggregation

create table transactions (
id integer not null,
tdate date not null,
opening_bal number(8,2) not null,
closing_bal number(8,2) not null,
transaction number(8,2) not null,
constraint pk_transactions primary key (id,tdate));

insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'01-jan-10',0,10,10);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'02-jan-10',10,20,10);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'03-jan-10',20,25,5);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'04-jan-10',25,-15,-40);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'05-jan-10',-15,0,15);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'06-jan-10',0,13,13);


SQL> select * from transactions;

        ID TDATE     OPENING_BAL CLOSING_BAL TRANSACTION
---------- --------- ----------- ----------- -----------
         1 01-JAN-10           0          10          10
         1 02-JAN-10          10          20          10
         1 03-JAN-10          20          25           5
         1 04-JAN-10          25         -15         -40
         1 05-JAN-10         -15           0          15
         1 06-JAN-10           0          13          13

6 rows selected.
SQL> select id, count(tdate) as tcount
  ,max(opening_bal) keep (dense_rank first order by tdate) as opening_bal
  ,max(closing_bal) keep (dense_rank last order by tdate) as closing_bal 
from transactions 
where tdate between '02-jan-10' and '04-jan-10' 
group by id;

        ID TCOUNT       OPENING_BAL CLOSING_BAL
---------- ------------ ----------- -----------
         1            3          10         -15

SQL>

Oracle How to generate random data (without PL/SQL)

This is a handy way of generating random data. The number of rows generated is defined by the "connect by level" (currently set to 5).

column s format A5
column r format 999999999999

select level,dbms_random.random r,dbms_random.value(1,10) v,dbms_random.string('','5') s,sysdate+level d
from dual
connect by level <= 5;

LEVEL     r               V           s      D        
---------- ------------- ---------- ----- ---------
         1     384454788 5.86831193 PBZVI 14/AUG/10
         2     568121861 8.77328309 YHVDP 15/AUG/10
         3    -918508229 6.24565917 CHKXW 16/AUG/10
         4    1409236401 4.15254419 HAZUS 17/AUG/10
         5   -1356731387 4.12237254 HDDDX 18/AUG/10

5 rows selected.

Oracle How to create a user

SQL> create user DEV identified by DEV;
User created.
SQL> grant create session to dev;
Grant succeeded.
SQL> grant resource to DEV;
Grant succeeded.
SQL> grant select any table to dev;
Grant succeeded.
SQL> grant select_catalog_role to dev;
Grant succeeded.
SQL>

Oracle: How to convert to local time

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select
cast(from_tz(cast (to_date('2011-01-01 16:00:00') as timestamp),'GMT') at local as date) as LOCAL_EVENT_DATE
from dual;
alter session set succeeded.
LOCAL_EVENT_DATE
-------------------------
2011-01-02 02:00:00
1 rows selected