sql06 - 6 Subqueries Objectives After completing this...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 6 Subqueries Objectives After completing this lesson, you should be able to do the following: Describe the types of problems that subqueries can solve Define subqueries List the types of subqueries Write single-row and multiple-row subqueries 6-2 Using a Subquery to Solve a Problem "Who has a salary greater than Jones's?" Main Query ? "Which employees have a salary greater than Jones's salary?" Subquery ? "What is Jones's salary?" 6-3 Subqueries SELECT FROM WHERE select_list table expr operator (SELECT FROM select_list table); The subquery (inner query) executes once before the main query. The result of the subquery is used by the main query (outer query). 6-4 Using a Subquery SQL> SELECT ename 2 FROM emp 2975 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE empno=7566); ENAME ---------KING FORD SCOTT 6-5 Guidelines for Using Subqueries Enclose subqueries in parentheses. Place subqueries on the right side of the comparison operator. Do not add an ORDER BY clause to a subquery. Use single-row operators with singlerow subqueries. Use multiple-row operators with multiple-row subqueries. 6-6 Types of Subqueries Single-row subquery Main query Subquery returns CLERK Multiple-row subquery Main query Subquery returns CLERK MANAGER Multiple-column subquery Main query Subquery 6-7 returns CLERK 7900 MANAGER 7698 Single-Row Subqueries Return only one row Use single-row comparison operators Operator = > >= < <= <> 6-8 Meaning Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to Executing Single-Row Subqueries SQL> 2 3 4 5 6 7 8 9 10 SELECT FROM WHERE ename, job emp job = (SELECT FROM WHERE sal > (SELECT FROM WHERE CLERK job emp empno = 7369) 1100 AND sal emp empno = 7876); ENAME JOB ---------- --------MILLER CLERK 6-9 Using Group Functions in a Subquery SQL> SELECT 2 FROM 3 WHERE 4 5 ename, job, sal emp sal = (SELECT FROM 800 MIN(sal) emp); ENAME JOB SAL ---------- --------- --------SMITH CLERK 800 6-10 HAVING Clause with Subqueries The Oracle Server executes subqueries first. The Oracle Server returns results into the main query's HAVING clause. SQL> 2 3 4 5 6 7 SELECT FROM GROUP BY HAVING deptno, MIN(sal) emp deptno MIN(sal) > (SELECT FROM WHERE 800 MIN(sal) emp deptno = 20); 6-11 What Is Wrong with This Statement? SQL> SELECT empno, ename 2 FROM emp 3 WHERE sal = ow -r 4 (SELECT MIN(sal) e pl 5 FROM emp ltideptno); 6 GROUP BYu ry ue bq su ith rw ERROR: to ORA-01427: single-row ra subquery e one row op ow no rows selected -r le ng Si 6-12 m returns more than Will This Statement Work? SQL> SELECT ename, 2 FROM emp 3 WHERE job = 4 5 6 no rows selected job es (SELECT job u FROM emp al v WHERE ename='SMYTHE'); o ry ue bq Su ns ur et r n s 6-13 Multiple-Row Subqueries Return more than one row Use multiple-row comparison operators Operator IN ANY Meaning Equal to any member in the list Compare value to each value returned by the subquery Compare value to every value returned by the subquery ALL 6-14 Using ANY Operator in Multiple-Row Subqueries SQL> 2 3 4 5 6 7 SELECT FROM WHERE empno, ename, job 1300 1100 emp 800 sal < ANY 950 (SELECT sal FROM emp WHERE job = 'CLERK') job <> 'CLERK'; JOB --------SALESMAN SALESMAN AND EMPNO --------7654 7521 ENAME ---------MARTIN WARD 6-15 Using ALL Operator in Multiple-Row Subqueries SQL> SELECT 2 FROM 3 WHERE 4 5 6 EMPNO --------7839 7566 7902 7788 empno, ename, job 1566.6667 2175 emp 2916.6667 sal > ALL (SELECT avg(sal) FROM emp GROUP BY deptno); JOB --------PRESIDENT MANAGER ANALYST ANALYST ENAME ---------KING JONES FORD SCOTT 6-16 Correlated subqueries The subquery references a column from a table referred to in other in the parent table it is evaluated once for each row processed by parent statement the parent table column is used with the alias name inside the subquery 6-17 SQL> SELECT 2 FROM 3 WHERE 4 5 6 deptno, ename, sal emp e1 sal = (SELECT max(sal) FROM emp WHERE deptno = e1.deptno) ORDER BY deptno; 6-18 Multi- column Subqueries multicolumn subquery has more than one column in the SELECT clause of the subquery. SQL> 2 3 4 5 6 7 8 SELECT ord_date,cust_name,prod_name,quantity FROM customer c,product p,order o WHERE o.cust_id=c.cust_id AND o.prod_id = p.prod_id AND (o.ord_date,o.cust_id) IN (SELECT max(ord_date),cust_id FROM order WHERE cust_id="C001") 6-19 Top-N analysis subquery in FROM clause In-line view 6-20 Summary Subqueries are useful when a query is based on unknown values. SELECT FROM WHERE select_list table expr operator (SELECT select_list FROM table); 6-21 ...
View Full Document

Page1 / 21

sql06 - 6 Subqueries Objectives After completing this...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online