Creating a Calendar in a single SQL statement

A few days ago, somebody asked if it is possible to write a Calendar in a single SQL statement.

Here you go...

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",

hierarchical queries

A relational database does not store data in a hierarchical way. Then how do I get the data in a hierarchical manner? Here we get to know about how to use the hierarchical querying feature which Oracle has given. This article talks about how you can interpret the hierarchical query conceptually and build hierarchical queries catering your needs.

Using hierarchical queries, you can retrieve records from a table by their natural relationship. Be it a family tree or a employee/manager tree or what ever.