Interview Answer

Software suppliers are trying to make their software packages more ‘user-friendly’… Their best approach so far has been to take all the old brochures and stamp the words ‘user-friendly’ on the cover. Bill Gates

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.