Sorting on Null Values in Oracle

Problem:

You need to find a way to override the sorting of nulls so they appear where you want at the beginning or end of the report.

How to get the first N rows with Oracle

Problem:

You want to get the first ten rows of the result set in Oracle.

Enable case insensitive search in Oracle

Problem

You have loaded a significant amount of data that is in mixed case. You want to query as if it were all lowercase (or all uppercase).

Solution

If you are using Oracle Database 10g or higher, you can use the ALTER SESSION statement enable case-insensitive searching with the >, <, and = comparison operators. To enable case-insensitive searching, alter the NLS_SORT and NLS_COMP parameters:

alter session set nls_sort=binary_ci; alter session set nls_comp=linguistic;

Oracle WITH clause to simplify complex SQL

Oracle WITH  clause or subquery factoring clause provides a way to pre-materialize results of queries before statement executes. Also, Oracle WITH clause  is more efficient than using a subquery.