Get Nth highest salary from a table

U can use below queries to get nth highest sal from a table.

method 1 :


select * from (select distinct sal from scott.EMP order by sal desc) where rownum<=&n
minus
select * from (select distinct sal from scott.EMP order by sal desc) where rownum<=&n-1;

method 2:

SELECT DISTINCT sal
           FROM scott.EMP a
          WHERE &n - 1 = (SELECT COUNT (DISTINCT (sal))
                            FROM scott.EMP b
                           WHERE a.sal < b.sal);
                         

method 3 :

SELECT DISTINCT sal
           FROM scott.EMP a
          WHERE &n  = (SELECT COUNT (DISTINCT (sal))
                            FROM scott.EMP b
                           WHERE a.sal <= b.sal);
                           

the way the queries 2 and 3 works is by finding the number of salaries that are greater than the current salary being searched for. If that number is exactly equal to N-1, then we know that we have the Nth highest salary, otherwise we continue searching through the table. If this description does not make sense, then read on for a more detailed description, which should make sense to you.

if u want detailed info on this go through below link

http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/

method 4:


select level, max(sal) from scott.emp
where level = &n
connect by prior sal > sal
group by level;

select level, max(sal) from scott.emp
where level=5
connect by prior sal > sal
group by level

The difference between sequential data access and random data access files in oracle

There is no difference in the files, only in the way they are accessed. Sequential files are accessed just like they sound, sequentially, which means that if you want to read something that starts at byte number 40,000, you have to read and ignore the first 39,999 bytes. Random-access files, however, allow you to go directly to the exact location you want. 

LONG and LONG RAW data types = sequential data access

LOB Data types =random data access

Practice Papers For Learning License (LLR) Test

Learning License (LLR) Test

Planning to take LLR exam, just go through this sites.Here you will find many LLR sample test questions in both English and Telugu Languages.

http://vinayakadriving.blogspot.in/2011/06/learning-license-questions-llr.html

http://www.classicdrivingonline.com/images/english-test.pdf

http://aptransport.org/html/demotest.php

Exception raised while wrapping up a procedure Code Using Oracle9i Wrap Utility

CREATE TABLE TEST_TAB(COL1  NUMBER,COL2  NUMBER,COL3  VARCHAR2(100))

/

 Save below procedure as a .prc file

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

   INSERT INTO test_tab

      SELECT 1, (SELECT empno

                   FROM EMP

                  WHERE ename = 'ALLEN'), 'RAJU'

        FROM DUAL;

   COMMIT;

END;

/

Then try to wrap the procedure code using wrap command

wrap iname=TEST_PROC.prc oname=TEST_PROC_w.prc;

Processing TEST_PROC.prc to TEST_PROC_w.prc

PSU(103,1,5,18):Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null others <an identifier>

   <a double-quoted delimited-identifier> <a bind variable> avg

   count current exists max min prior sql stddev sum variance

   execute forall merge time timestamp interval date

   <a string literal with character set specification>

   <a number> <a single-quoted SQL string> pipe

PSU(103,1,7,40):Encountered the symbol ")" when expecting one of the following:

   . ( * @ % & - + ; / at for mod rem <an exponent (**)> and or

   group having intersect minus order start union where connect

   ||

PL/SQL Wrapper error: Compilation error(s) for:

CREATE OR REPLACE PROCEDURE test_proc

Outputting source and continuing.

Cause:-

--------

Above exception is why because in one select statement another select is used and after that trying to insert some constant value.

Solution:-

---------

Way1:- Convert normal sql statement as a Dynamic Sql statement.

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

  execute immediate 'INSERT INTO test_tab

      SELECT 1, (SELECT empno

                   FROM EMP

                  WHERE ename = ''ALLEN''), ''RAJU''

        FROM DUAL';

   COMMIT;

END;

/

Way2:- Put it in a single select statement

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

   INSERT INTO test_tab

       SELECT 1,empno,'RAJU'

                   FROM EMP

                  WHERE ename = 'ALLEN';   

   COMMIT;

END;

/

Why in dba_constarints index_name is null for some primary constraints

Oracle 9iR2

-------------------

When you create a primary constraint automatically 1 row will be inserted into DBA_CONSTRAINTS and another will be into DBA_INDEXES. And in DBA_CONSTRAINTS index_owner and index_name columns will be populated with index which is pointed to primary key.

When u try to disable that constraint, then index_owner and index_name will become null in dba_constraints and row will be deleted from dba_indexes.

When u enable back the constraint, then again index_owner and index_name will become populated in dba_constraints and row will be inserted into dba_indexes.

NOTE:-when u disable and enable it back your user defined index name will be lost and it will be replaced with primary key name.(u can check this in below example).

SQL> CREATE TABLE TEST_INDEX(NUM NUMBER,NUM1  NUMBER);

Table created.

SQL> CREATE UNIQUE INDEX TEST_INDEX_IDX ON TEST_INDEX(NUM);

Index created.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

INDEX_NAME                     TABLE_NAME

------------------------------ ------------------------------

TEST_INDEX_IDX                 TEST_INDEX

SQL> ALTER TABLE TEST_INDEX ADD CONSTRAINT TEST_INDEX_PK PRIMARY KEY (NUM);

Table altered.

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

------------------------------ ------------------------------ ------------------------------

TEST_INDEX                     TEST_INDEX_PK                  TEST_INDEX_IDX

SQL> ALTER TABLE TEST_INDEX DISABLE CONSTRAINT TEST_INDEX_PK;

Table altered.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

no rows selected

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

------------------------------ ------------------------------ ------------------------------

TEST_INDEX                     TEST_INDEX_PK

SQL> ALTER TABLE TEST_INDEX ENABLE CONSTRAINT TEST_INDEX_PK;

Table altered.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

INDEX_NAME                     TABLE_NAME

------------------------------ ------------------------------

TEST_INDEX_PK                  TEST_INDEX

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

------------------------------ ------------------------------ ------------------------------

TEST_INDEX                     TEST_INDEX_PK                  TEST_INDEX_PK

SQL>

Oracle 11g

-------------------

When u try to disable that constraint, then index_owner and index_name will become null in dba_constraints and row will not be deleted from dba_indexes.

To resolve this: neither u have to drop the index manually nor disable constraint with cascade option.

SQL> ALTER TABLE TEST_INDEX DISABLE CONSTRAINT TEST_INDEX_PK cascade;