Interview Answer

If McDonalds were run like a software company, one out of every hundred Big Macs would give you food poisoning, and the response would be, ‘We’re sorry, here’s a coupon for two more.’ “ Mark Minasi

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.