sql06-contains inline views also

sql06-contains inline views also - 6 Subqueries Objectives...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right 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 After be able to do the following: be • 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 ? 6 -3 “What is Jones’s salary?” 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 returns CLERK • Multiple-row subquery Main query Subquery returns returns CLERK MANAGER • Multiple-column subquery Main query Subquery 6 -7 returns returns CLERK 7900 MANAGER 7698 Single-Row Subqueries • Return only one row • Use single-row comparison operators Operator Meaning = > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> 6 -8 Equal to Not equal to Executing Single-Row Subqueries SQL> 2 3 4 5 6 7 8 9 10 SELECT FROM WHERE AND ename, job emp job = (SELECT FROM WHERE sal > (SELECT FROM WHERE ENAME JOB ---------- --------MILLER CLERK 6 -9 CLERK job emp empno = 7369) 1100 sal emp empno = 7876); Using Group Functions in a Subquery SQL> SELECT 2 FROM 3 WHERE 4 5 ename, job, sal emp sal = (SELECT FROM ENAME JOB SAL ---------- --------- --------SMITH CLERK 800 6-10 800 MIN(sal) emp); 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 6-11 SELECT FROM GROUP BY HAVING deptno, MIN(sal) emp deptno MIN(sal) > (SELECT FROM WHERE 800 MIN(sal) emp deptno = 20); What Is Wrong with This Statement? ry ue bq su 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 m ith rw ERROR: to ORA-01427: single-row ra subquery e one row op ow no rows selected -r le ng Si 6-12 returns more than Will This Statement Work? SQL> SELECT ename, 2 FROM emp 3 WHERE job = 4 5 6 no rows selected 6-13 job s es (SELECT job u FROM emp al v WHERE ename='SMYTHE'); o ry ue bq Su ns ur et r n Multiple-Row Subqueries • Return more than one row • Use multiple-row comparison operators Operator Meaning IN Equal to any member in the list ANY Compare value to each value returned by the subquery ALL 6-14 Compare value to every value returned by the subquery Using ANY Operator in Multiple-Row Subqueries SQL> 2 3 4 5 6 7 SELECT FROM WHERE AND EMPNO --------7654 7521 6-15 empno, ename, job 1300 1100 emp 800 sal < ANY 950 (SELECT sal FROM emp WHERE job = 'CLERK') job <> 'CLERK'; ENAME ---------MARTIN WARD JOB --------SALESMAN SALESMAN Using ALL Operator in Multiple-Row Subqueries SQL> SELECT 2 FROM 3 WHERE 4 5 6 EMPNO --------7839 7566 7902 7788 6-16 empno, ename, job 1566.6667 2175 emp 2916.6667 sal > ALL (SELECT avg(sal) FROM emp GROUP BY deptno); ENAME ---------KING JONES FORD SCOTT JOB --------PRESIDENT MANAGER ANALYST ANALYST The ALL operator compares a value to every value returned by a subquery. The example above displays employees whose salary is greater than the average salaries of all the departments. The highest average salary of a department is $2916.66, so the query returns those employees whose salary is greater than $2916.66. • >ALL means more than the maximum and <ALL means less than the minimum. • The NOT operator can be used with IN, ANY, and ALL operators. 6-17 Correlated subqueries • The subquery references a column from a table referred to in other in the parent table table • it is evaluated once for each row processed by parent statement processed • the parent table column is used with the alias name inside the subquery alias •those that refer to a column value from the those outer query outer •Correlated subqueries are often use with the Correlated EXIST (or NOT EXISTS) operator EXIST 6-18 •Inner query executed once for each row Inner processed by outer query processed •Inner query references row contained in Inner outer query outer 6-19 SQL> SELECT 2 FROM 3 WHERE 4 5 6 6-20 deptno, ename, sal emp e1 sal = (SELECT max(sal) FROM emp WHERE deptno = e1.deptno) ORDER BY deptno; Corelated subquery is the subquery which depend upon the main query for its completion. For eg:­ If you want to find out the salary of all employees whose salary is greater than the average salary of their respective departments, you can issue the following corelated subquery. Eg:­ SELECT ename,sal FROM emp a WHERE a.sal > (SELECT avg(sal) FROM emp b WHERE b.deptno = a.deptno); 6-21 Multiple-Column Subquery In multiple-column subqueries, rows in the subquery results are evaluated in the main query in pair-wise comparison. That is, column-to-column comparison and row-to-row comparison. 6-22 •Returns more than one column in results •Can return more than one row •Column list on left side of operator must Column be in parentheses be •Uses IN operator for WHERE and HAVING Uses clauses clauses 6-23 Example-Multiple Column Subquery select empno, ename, job, sal, deptno from emp where (sal, deptno) in (select sal, deptno from emp where sal between 1000 and 2000 and deptno between 20 and 40) order by empno 6-24 EMPNO ENAME JOB SAL DEPTNO --------- ---------- --------- ----------------7369 SMITH ANALYST 1500 20 7499 ALLEN SALESMAN 1000 30 7521 WARD SALESMAN 1000 30 7566 JONES MANAGER 2000 20 7698 BLAKE MANAGER 2000 30 7788 SCOTT ANALYST 2000 20 7844 TURNER SALESMAN 1000 30 7876 ADAMS ANALYST 1500 20 In this case, we selected empno, ename, job, sal, and deptno from the ‘emp’ table such that all those employees whose salary was between 1000 and 2000 and their deptno was between 20 and 40. A glance at the data allows us to verify that all records returned by the query satisfy those conditions. Note also that the records are ordered by the empno as dictated by the last line of the subquery. 6-25 Multi- column Subqueries • multicolumn subquery has more than one column in the SELECT clause of the subquery. subquery. SQL> 2 3 4 5 6 7 8 6-26 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”) Summary Subqueries are useful when a query is Subqueries based on unknown values. based SELECT FROM WHERE 6-27 select_list table expr operator (SELECT select_list FROM table); Summary ANY AND ALL OPERATORS ANY COMBINED WITH ARITHMETIC OPERATORS OPERATORS 6-28 Nested Subqueries •Maximum 255 subqueries if nested in Maximum WHERE clause WHERE •No limit if nested in FROM clause •Innermost subquery resolved first, then Innermost next level, etc. next 6-29 Top-N analysis • subquery in FROM clause subquery • In-line view In-line 6-30 Using in-line view for speed. Facts: A subquery answers multiple-part questions subquery For example: •To determine who works in Taylor's To department, you can first use a subquery to determine the department in which Taylor works works You can then answer the original question You with the parent SELECT statement. 6-31 A subquery in the FROM clause of a subquery FROM SELECT statement is also called an inline SELECT view. A subquery in the WHERE clause of a WHERE SELECT statement is also called a nested SELECT subquery. subquery. 6-32 ...
View Full Document

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Ask a homework question - tutors are online