Interview Answer

Correctness is clearly the prime quality. If a system does not do what it is supposed to do, then everything else about it matters little. Bertrand Meyer

What is the difference between WHERE clause and HAVING clause

January 1, 1970 12:00 am

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.