Interview Answer

Any fool can write code that a computer can understand. Good programmers write code that humans can understand. Martin Fowler

Display employee records who gets more salary than the average salary in the department?

January 1, 1970 12:00 am

Comments



Display employee records who gets more salary than the average salary in the department?


The avg function returns average value of a numeric column.


													desc HR.employees;
													
													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)   
													
desc HR.departments;

Name            Null     Type         
--------------- -------- ------------ 
DEPARTMENT_ID   NOT NULL NUMBER(4)    
DEPARTMENT_NAME NOT NULL VARCHAR2(30) 
MANAGER_ID               NUMBER(6)    
LOCATION_ID              NUMBER(4)  

Solution

   					 Select * from HR.employees where salary>(select avg(salary) from HR.departments
 dept, HR.employees employee 
 where dept.DEPARTMENT_ID = employee.DEPARTMENT_ID);				    

Comments



Please login to add comments.