1 / 17

6 Sub queries

6 Sub queries. Important Legal Notice: Materials on this lecture are from a book titled “Oracle Education” by Kochhar , Gravina , and Nathan (1999), published by Oracle Corp. For further information, visit www.oracle.com

umay
Download Presentation

6 Sub queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 6 Subqueries Important Legal Notice: Materials on this lecture are from a book titled “Oracle Education” by Kochhar, Gravina, and Nathan (1999), published by Oracle Corp. For further information, visit www.oracle.com This presentation must be used for only education purpose for students at Lake Superior State Universitywhich has used Oracle systems as a database platform embedded on its ERP systems.

  2. 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

  3. Using a Subquery to Solve a Problem “Who has a salary greater than Jones’ salary?” Main Query “Which employees have a salary greater than Jones’ salary?” Subquery ? “What is Jones’ salary?”

  4. Subqueries SELECT select_list FROM table WHERE expr operator ( SELECT select_listFROM table); • The subquery (inner query) executes once before the main query. • The result of the subquery is used by the main query (outer query).

  5. Using a Subquery SQL> SELECT ename 2 FROM emp3 WHERE sal > 4 5 6 ( SELECT salFROM emp WHERE empno=7566 ); ENAME-------------------- KINGFORDSCOTT Display employees who make more salary than the employee (7566)’ salary.

  6. Guidelines for Using Subqueries • Enclose subqueriesin 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 single-row subqueries. • Use multiple-row operators with multiple-row subqueries.

  7. Types of Subqueries • Single-row subquery Main query returns Subquery CLERK • Multiple-row subquery Main query returns Subquery CLERK MANAGER • Multiple-column subquery Main query returns Subquery CLERK 7900 MANAGER 7698

  8. Single-Row Subqueries • Return only one row • Use single-row comparison operators Practice: Display the employees whose job title is the same as that of employee 7369.

  9. Executing Single-Row Subqueries Display employees whose job title is the same as that of employee 7369, and whose salary is greater than that of employee 7876. SQL> SELECT ename, job 2 FROM emp3 WHERE job = 4 5 6 7ANDsal >8 9 10 ( SELECT job FROM emp WHERE empno = 7369 ) ( SELECT sal FROM emp WHERE empno = 7876 ); ENAME JOB -------------------- ------------------- MILLER CLERK

  10. Using Group Functionsin a Subquery SQL> SELECT ename, job, sal 2 FROM emp3 WHERE sal =4 5 ( SELECT MIN (sal) FROM emp); ENAME JOB SAL ------------- ----------- -------- MILLER CLERK 800 Display the employee name, job title, and salary of all employees whose salary is equal to the minimum salary.

  11. HAVING Clause with Subqueries • The Oracle Server executes subqueries first. • The Oracle Server returns the results into the HAVING clause of the main query. SQL> SELECT deptno, MIN(sal) 2 FROM emp3 GROUP BY deptno 4 HAVING MIN(sal) > 5 67 800 ( SELECT MIN(sal) FROM emp WHERE deptno=20); Display all the departments that have a minimum salary greater than that of DEPARTMENT 20.

  12. What is Wrong with This Statement? SQL> SELECT empno, ename 2 FROM emp3 WHERE sal = 4 5 6 Single-row operator with multiple-row subquery ( SELECT MIN(sal) FROM empGROUP BY deptno); ERROR: ORA-01427: single-row subquery returns more than one row no rows selected

  13. Will This Statement Work? SQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE ename=‘SMYTHE’); Subquery returns no values No rows selected

  14. Multiple-Row Subqueries • Return more than one row • Use multiple-row comparison operators

  15. Using ANY Operator in Multiple-Row Subqueries SQL> SELECT empno, ename, job 2 FROM emp3 WHERE sal< ANY 4 5 6 7 AND job <> ‘CLERK’; 1300, 1100, 800, 950 (SELECT sal FROM emp WHERE JOB = ‘CLERK’) EMPNO NAME JOB----------- -------------- ------------------7654 MARTIN SALESMAN Display employees whose salary is less than any clerk and who are not clerks.

  16. Using “ALL” Operator in Multiple-Row Subqueries SQL> SELECT empno, ename, job2 FROM emp3 WHERE sal > ALL 4 5 6 1566.67, 2175, 2916.67 ( SELECT avg(sal) FROM emp GROUP BY deptno ); EMPNO ENAME JOB ----------- --------------- ------------------ 7839 KING PRESIDENT7566 JONES MANAGER7902 FORD ANALYST7788 SCOTT ANALYST Display employees whose salary is greater than the average salaries of all the departments.

  17. Summary Subqueries are useful when a query is based on unknown values. SELECT select_list FROM table WHERE expr operator ( SELECT select_list FROM table );

More Related