some useful query

Use of Extract Function.
SELECT DATE;

 

Date      
 ----------
 2011-09-22 

 

SELECT EXTRACT (YEAR FROM date) as "year";

year   
 -------
 2011    

 

SELECT EXTRACT (MONTH FROM date) as "month";

month   
 --------
 9       

To get the current system date.

select DATE;

To get the current system time and current session ‘Time Zone’ displacement.
select CURRENT_TIME;

To get all the objects in a database.
help database databasename;

It returns the current system timestamp (including year, month and day) and current session Time Zone displacement.
select CURRENT_TIMESTAMP;

 To get the amount of space occupy by each table in there respective Database.

select databasename,
       tablename,
       CAST ( sum(currentperm)/1024/1024/1024 AS DECIMAL(38,2) ) AS "SIZE(GB)"
from dbc.tablesize
where databasename = 'DATABASENAME'
group by databasename , tablename;

 To find all the Database and the amount of space used by it.

SELECT DatabaseName,
       SUM(MaxPerm)/(1024*1024*1024) (DECIMAL(15,6)) as "Max Perm (GB)",
       SUM(CurrentPerm)/(1024*1024*1024) (DECIMAL(15,6)) as "Current Perm (GB)",
       ((SUM(CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100) (DECIMAL(15,6)) as "Percent Used"
FROM DBC.DiskSpace
WHERE MAXPERM >0
GROUP BY 1
ORDER BY 4 DESC;