Interview Answer

There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult. C.A.R. Hoare

What is the difference between WHERE clause and HAVING clause

April 10, 2016 9:24 pm

Comments



What is the difference between WHERE clause and HAVING clause


WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns

													
													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 EMPLOYEE_ID > 200

											SELECT dept.DEPARTMENT_NAME, avg(emp.salary) AVG_SAL
											FROM HR.departments dept, HR.employees emp
											WHERE dept.DEPARTMENT_id = emp.DEPARTMENT_id (+)
											GROUP BY dept.DEPARTMENT_NAME
											HAVING AVG(emp.salary) > 80
																	    

Comments



Please login to add comments.