12 Selects of christmas (part 1)

Since we are still in that festive spirit, here's a four parter on 12 different things to do with a SELECT (which is a nice alternative to 12 things to do with leftover turkey)

1. Plain select with a side order of flashback

You can't really start anywhere other than a plain select. However to add some flavour, I've include the FLASHBACK clause.

create table test_tab (id number, val varchar2(100));

begin
dbms_lock.sleep(60);
  -- In 9i you may have to wait three minutes, not just one.
end;
.
/

insert into test_tab
select rownum, to_char(add_months(sysdate,rownum),'fmMonth')
from all_tables
where rownum <= 12; 
commit;
SELECT * from test_tab AS OF TIMESTAMP SYSDATE-(1/(24*60)); 

Now, try this one.

delete from test_tab where rownum < 5;
select * from test_tab;
select * from test_tab as of
 SCN dbms_flashback.get_system_change_number; 

Using FLASHBACK to go to the current time/SCN allows you to select the current COMMITTED state of the table, that is without all the changes your session has made. An interesting feature, but I am unable to think of any practical use for it.

2. Inline View

Here I'll show the first and last days of the months in the current year.
Rather than repeat the TO_DATE(...rownum...) operation for each column, I'll select that in an inline view.

select to_char(val_date,'DD-Mon-YYYY') first_date,
      to_char(last_day(val_date),'DD-Mon-YYYY') last_date
from (select to_date('01-'||rownum||'-2005','DD-MM-YYYY') val_date
     from all_objects where rownum <=12) order by val_date;

3. Scalar subquery

Now how many objects did I create in each month.

select to_char(val_date,'DD-Mon-YYYY') first_date,
      to_char(last_day(val_date),'DD-Mon-YYYY') last_date,
      (select count(*) from user_objects
      where created >= val_date
      and created < add_months(val_date,1)) num_obj
from (select to_date('01-'||rownum||'-2005','DD-MM-YYYY') val_date
    from all_objects where rownum <=12)
order by val_date;