Oracle SQL's

Nth Highest Salary

Using analytic functions: rank() or dense_rank():

SQL> select *
2 from
3 (
4 select ename
5 ,sal
6 ,rank() over (order by sal desc) ranking
7 from emp
8 )
9 where ranking = 4 -- Replace 4 with any value of N
10 /

ENAME SAL RANKING
---------- ---------- ----------
JONES 2975 4


SQL> select *
2 from
3 (
4 select ename
5 ,sal
6 ,dense_rank() over (order by sal desc) ranking
7 from emp
8 )
9 where ranking = 4 -- Replace 4 with any value of N
10 /

ENAME SAL RANKING
---------- ---------- ----------
BLAKE 2850 4
CLARK 2850 4

There are other approaches for calculating the Nth highest row, too. The next is a non-analytic approach, which works the same way as the RANK query (gaps for ties).

SQL> select ename
2 , sal
3 from emp a
4 where 3 = ( select count(*) -- Replace 3 with any value of (N - 1)
5 from emp b
6 where b.sal > a.sal)
7 /

ENAME SAL
---------- ----------
JONES 2975

How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);

Example:
delete from Employee where rowid not in (select max(rowid) from Employee group by emp_id);

Display the number value in Words

select salary, (to_char(to_date(salary,'j'), 'jsp')) from employees;

Output:
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty

Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5

Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor

Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’

What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.

What is the maximum number of triggers, can apply to a single table?
12 triggers.