Week 4.pdf

Info icon This preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
EECS 116 & CS 122A Discussion - Week 4
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
HAVING Clause The HAVING clause is used along with the GROUP BY claus e to select some of the groups The predicate in the HAVING clause is applied after the for mation of groups whereas predicates in the WHERE clause are applied before forming groups List the department name and the number of employees f or 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;
Image of page 2
Find the names and average salaries of all departm ents w hose average salary is greater than 42000 select dept_name , avg ( salary ) from instructor group by dept_name having avg ( salary ) > 42000; HAVING Clause
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Nested Subqueries SQL provides a mechanism for the nesting of subqueries. A subquery is a select-from-where expression that is nested within another query. A common use of subqueries is to perform tests for set membershi p, set comparisons, and set cardinality.
Image of page 4