Interview Answer

In software, we rarely have meaningful requirements. Even if we do, the only measure of success that matters is whether our solution solves the customerís shifting idea of what their problem is. Jeff Atwood

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

April 10, 2016 6:16 pm

Comments



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
           ));				    

Comments



Please login to add comments.