What is the query to find nth highest salary?
What is the use of cursors?
Answer Posted / suraj kedia
There are 3 ways to find out the nth highest salary in a
given table (e.g. emp) as below;
1) select distinct sal from emp e1 where &n=(select count
distinct sal from emp e2 where e1.sal <=e2.sal);
2) select empno,enaame,sal,deptno,rank () over (order by sal
desc) as ra from emp where ra=&n;
3) select empno,enaame,sal,deptno,dense_rank () over (order
by sal desc) as ra from emp where ra=&n;
All the above methods are tried n tested....it works
successfully.
What is a Cursor.
When a query is executed in oracle, a result set is produced
and stored in the memory. Oracle allows the programmer to
access this result set in the memory through cursors.
Why use a cursor?
Many times, when a query returns more than one row as a
result, we might want to go through each row and process the
data in different way for them. Cursor is handy here.
Types of cursors:
Oracle PL/SQL declares a cursor implicitly for all queries
and DML statements (including queries that return only one
row). But in most of the cases we don’t use these cursors
for queries that return one row. Based on this, there are 2
types of cursors
1. Explicit cursor
2. Implicit cursor
Is This Answer Correct ? | 13 Yes | 2 No |
Post New Answer View All Answers
Write the different tools in the workflow manager?
What is meant by LDAP users?
How to create different types of slowly changing dimensions (SCD) in informatica using the mapping wizard?
What does “tail –f” command do and what is its use as an Informatica admin.
What is the need for an etl tool?
What are the different types of olap? Give an example?
What will happen if the select list columns in the custom override sql query and the output ports order in sq transformation do not match?
How can we send the flat file data to different targets(which are also flat files) based on the name of flat file with out adding extra columns?
How do you load only null records into target?
What happen if you have 3 ports in SQ and 4 ports in SQL override of SQ( provided all ports are in same order and they are connected with proper source and target)? Also what happens when I have 4 ports and will extract 3 values in SQloverride.. What will be the value in 4th port ?
How to load last n records of file into target table - informatica
what is persistent lookup cahce?how to use and when to use this persistent lookup cahce?explain?
what are the Methods For Creating Reusable Transformations?
How many input parameters can exist in an unconnected lookup?
What are the transformations that are not supported in mapplet?