Notes for Oracle SQL Usage


General Functions

Regardless of the logic purpose of the following codes, let go through each function that have been used within them


Round(Avg(To_Char(SysDate-7* RowNum,’ DD Month YYYY’)))


SYSDATE: Returns the current date-time from the server where the

database is located


TO_CHAR(date,format): Converts a date to a string in the specified format


ROWNUM: is an Oracle pseudo column which numbers the rows in a result set, which you can use directly within your SQL query. With SysDate-7* RowNum, you would get a list of dates that has an interval of a week time.


AVG(expression) : Returns the average of the values in a set of rows


ROUND(number, decimal places): Rounds a value to the specified number of decimal places




CEIL(number): number is the value used to find the smallest integer value. See the difference against Round();


DECODE: has the functionality of an IF-THEN-ELSE statement. The value returned by Ceil() will be checked against each following parameter. So, if it’s 1, the decode() function will return ‘1’. else if 2, return ‘2’. else if 3, return, if neither of the preceding ones it will all be ‘4’.




TRUNC(datetime): Removes the time component from a date-time value


ADD_MONTHS(date, number ofmonths): Adds the specified number of months to the date value (subtracts months if the number of months is negative)



Cascaded Select

Cascaded select query is very often used for query base on the records set which need to be collected by other queries. Like the following example, the select query needs to be based on the records sets a and b, which need to be collected by the two inner queries.



a AS(

            SELECT …


,b As(

            SELECT …



FROM a, b


One more thing need to be aware is the alias. It is recommended to provides alias for columns that need to be returned by the inner query, although they could be referred by their column name by default.


Outer Joins

Joins could be specified either in the From clause or in the Where clause. The difference is the expression. (used by oracle 9i and after)

Select *

from a,

left out join b on a.placeId=b.placeId


The preceding code will be also expressed by the following code. (used by oracle8i and ealier, see

Select * from a, b

Where a.placeId =(+)b.placeId



        Select Distinct

        DE.Display_Name Company


        ,First_Value(CR.Published_Date) Over(Partition By DE.Display_Name Order By CRP.Purchase_Date Desc)


        From CompanyReportPurchase CRP, CompanyReport CR, DirectoryEntry DE

        Where CR.Report_ID=CRP.Report_ID


The highlighted code above is trying to fetch the CR.Published_Date value from first row (by First_value) of the records returned by the later part. The code inside the Over() function is running based on the records returned by the where clause. Inside the Over() function, the Partition By DE.Display_Name will subgroup the records by DE.Display_Name and order them by CRP.Purchase_Date. 


The general syntax of this analytic function could be concluded as:


Function(arg1,…, argn) OVER ( [PARTITION BY <…>] [ORDER BY <….>] [<window_clause>] )





This entry was posted in Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s