Interview Answer

A programmer is a person who passes as an exacting expert on the basis of being able to turn out, after innumerable punching, an infinite series of incomprehensive answers calculated with micrometric precisions from vague assumptions based on debatable figures taken from inconclusive documents and carried out on instruments of problematical accuracy by persons of dubious reliability and questionable mentality for the avowed purpose of annoying and confounding a hopelessly defenseless department that was unfortunate enough to ask for the information in the first place. Grid newsmagazine

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.