Database Management System

Oracle SQL Interview Questions

Pinterest LinkedIn Tumblr

Write a SQL query to get the second highest salary from the employee table

The employee table contains the following fields

Name           Null     Type        
-------------- -------- ------------
EMPLOYEE_ID    NOT NULL NUMBER(6)   
FIRST_NAME              VARCHAR2(20)
LAST_NAME      NOT NULL VARCHAR2(25)
EMAIL          NOT NULL VARCHAR2(25)
PHONE_NUMBER            VARCHAR2(20)
HIRE_DATE      NOT NULL DATE        
JOB_ID         NOT NULL VARCHAR2(10)
SALARY                  NUMBER(8,2) 
COMMISSION_PCT          NUMBER(2,2) 
MANAGER_ID              NUMBER(6)   
DEPARTMENT_ID           NUMBER(4) 

The select query return the following results ( more data. We filtered the data)

100 Steven  King    SKING   515.123.4567    17-JUN-03   AD_PRES 24000           90
101 Neena   Kochhar NKOCHHAR    515.123.4568    21-SEP-05   AD_VP   17000       100 90
102 Lex De Haan LDEHAAN 515.123.4569    13-JAN-01   AD_VP   17000       100 90
103 Alexander   Hunold  AHUNOLD 590.423.4567    03-JAN-06   IT_PROG 9000        102 60
104 Bruce   Ernst   BERNST  590.423.4568    21-MAY-07   IT_PROG 6000        103 60

Solution

SELECT  max(salary) FROM (
            SELECT salary FROM HR.employees
            WHERE salary NOT IN (
              SELECT max(salary) FROM HR.employees
           ));          
1 2 3 4 5 6 7 8 9 10

Write A Comment