HAVINGClause The HAVING clause is used along with the GROUP BY clause to select some of the groups The predicate in the HAVING clause is applied afterthe formation of groups whereas predicates in the WHERE clause are applied beforeforming groups List the department name and the number of employees for those departments with multiple employees. emp (ename, dno, sal) ename dno sal Jack 111 50K Alice 111 90K Lisa 222 80K Tom 333 70K Mary 333 60K dept(dno, dname, mgr) dno dname mgr 111 Sells Alice 222 Toys Lisa 333 Electronics Mary SELECT dname, count(*) FROM emp, dept WHERE emp.dno = dept.dno GROUP BY dname HAVING count(*) >1;
Find the names and average salaries of all departments w hose average salary is greater than 42000 select dept_name, avg (salary) from instructor group by dept_name having avg (salary) > 42000; HAVINGClause
has intentionally blurred sections.
Sign up to view the full version.
Nested Subqueries SQL provides a mechanism for the nesting of subqueries. A subqueryis a select-from-whereexpression that is nested within another query. A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality.