Shulou(Shulou.com)06/01 Report--

1. Overview of subqueries of Oracle what is a subquery? A subquery is a SELECT statement embedded in other SQL statements, also known as a nested query. Locations where subqueries can be used: where, select list, having, from. A. Use a subquery in the where clause as an example: query the employees who are in the same position as FORD: select * from emp where job = (select job from emp where ename='FORD'); SQL > select * from emp where job = (select job from emp where ename='FORD') EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7951 EASON ANALYST 7566 01-December-17 3000 20 7788 SCOTT ANALYST 7566 19-April-87 3000 20 7902 FORD ANALYST 7566 03 -December-81 3000 20b, Use a subquery example in the select clause: query the number, name, location, and number of departments of each department. Example: select deptno, dname, loc, (select count (empno) from emp where emp.deptno = dept.deptno) count from dept;SQL > select deptno, dname, loc, (select count (empno) from emp where emp.deptno = dept.deptno) count from dept DEPTNO DNAME LOC COUNT 10 ACCOUNTING NEW YORK 3 20 RESEARCH DALLAS 6 30 SALES CHICAGO 6 40 OPERATIONS BOSTON 0c, use a subquery in the having clause for example: query the employee information table, group by department number, and require to show the employee's department number and average salary When querying the conditions, the average wage is greater than the maximum wage of department 30. SQL > select deptno, avg (sal) from emp group by deptno having avg (sal) > (select max (sal) from emp where deptno = 30), DEPTNO AVG (SAL) 10 2916.66667d, use a subquery in the from clause to treat the result of the subquery as a new table. Example: query and display employee information that is higher than the department average salary. SQL > select ename, job, sal from emp, (select deptno, avg (sal) avgsal from emp group by deptno) dept where emp.deptno = dept.deptno and sal > avgsal;ENAME JOB SALEASON ANALYST 3000ALLEN SALESMAN 1600JONES MANAGER 2975BLAKE MANAGER 2850SCOTT ANALYST 3000KING PRESIDENT 5000FORD ANALYST 3000 has selected 7 rows. 2. What are the main query and subquery of Oracle?

A, a main query can have multiple subqueries for example: display information about employees whose positions are the same as those in 7521 and whose salary is greater than 7934. SQL > select * from emp where job = (select job from emp where empno = 7521) and sal > (select sal from emp where empno = 7934) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7499 ALLEN SALESMAN 7698 20-February-81 1600 300 30 7844 TURNER SALESMAN 7698 08-September-81 1500 030b. The execution order of subqueries is generally executed first. The main query is then executed, with the exception of related subqueries. For example: query the employee information in the employee table that is less than the average salary. SQL > select empno, ename, sal from emp where sal

< (select avg(sal) from emp); EMPNO ENAME SAL 7369 G_EASON 800 7499 ALLEN 1600 7521 WARD 1250 7654 MARTIN 1250 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7934 MILLER 1300已选择8行。c、相关子查询当子查询需要引用主查询的表列时，Oracle会执行相关查询。相关子查询是先执行主查询，在执行子查询。示例：查询工资高于部门平均工资的雇员名、工资和部门号：SQL>

Select ename, sal, deptno from emp e where sal > (select avg (sal) from emp where deptno = e.deptno); ENAME SAL DEPTNOEASON 3000 20ALLEN 1600 30JONES 2975 20BLAKE 2850 30SCOTT 3000 20KING 5000 10FORD 3000 20 has been selected. D, the main query and subquery may not be the same table. The main query and subquery can query different tables. Example: ACCOUNTING employee information when querying department names. SQL > select * from emp where deptno = (select deptno from dept where dname='ACCOUNTING') EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7782 CLARK MANAGER 7839 09-June-81 2450 10 7839 KING PRESIDENT 17-November-81 5000 10 7934 MILLER CLERK 7782 23-January-82 1300 103, Subqueries for Oracle: types of subqueries for single-row subqueries: single-row subqueries and multiple-row subqueries. Single-row subquery: a subquery statement that returns only one row of data. Use the single-line comparison operator: operator meaning = select ename > greater than > = greater than or equal to select ename, job, sal from emp where sal > = (select avg (sal) from emp); ENAME JOB SALEASON ANALYST 3000JONES MANAGER 2975BLAKE MANAGER 2850CLARK MANAGER 2450SCOTT ANALYST 3000KING PRESIDENT 5000FORD ANALYST 3000 has selected seven lines. Example 3: query employees whose department name is not 'SAVE'. SQL > select * from emp where deptno (select deptno from dept where dname= 'SALES'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7951 EASON ANALYST 7566 01-December-17 3000 20. 7902 FORD ANALYST 7566 03-December-81 3000 20 7934 MILLER CLERK 7782 23-January-82 1300 10 9 lines have been selected. B. Illegal use of single-row subquery examples: select ename, job, sal from emp where sal = (select max (sal) from emp group by deptno); SQL > select ename, job, sal from emp where sal = (select max (sal) from emp group by deptno); select ename, job, sal from emp where sal = (select max (sal) from emp group by deptno) error occurred in row 1: ORA-01427: single-row subquery returns multiple rows select max (sal) from emp group by deptno;sql statement returns multiple rows of data. SQL > select max (sal) from emp group by deptno;MAX (SAL) 2850 3000 50004, subquery of Oracle: multi-row subquery is a subquery statement that returns multiple rows of data. Use the multiline comparison operator: (you must use the multiline comparison operator when using multirow subqueries. The operator means that IN is equal to any ALL in the list and all the values returned by the subquery to compare ANY with any value returned by the subquery a, to use the IN operator in multiple subqueries example: query the employee information corresponding to the department where the work location is in NEW YORK and CHICAGO. SQL > select * from emp where deptno in (select deptno from dept where loc='NEW YORK' or loc=' CHICAGO'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 23-January-82 1300 10. 7698 BLAKE MANAGER 7839 01-May-81 2850 30 7654 MARTIN SALESMAN 7698 28-September-81 1250 1400 30 9 rows have been selected. B. example of using the ALL operator in a multiline subquery: query the employee name, salary, and department number that are higher than the salary of all employees in department 30. SQL > select ename, sal, deptno from emp where sal > all (select sal from emp where deptno = 30) ENAME SAL DEPTNOJONES 2975 20EASON 3000 20FORD 3000 20SCOTT 3000 20KING 5000 10c, use the ANY operator in multiple subqueries example: query the employee name, salary, and department number that are higher than any one of the employees in department 10. SQL > select ename, sal, deptno from emp where sal > any (select sal from emp where deptno = 10); ENAME SAL DEPTNOKING 5000 10EASON 3000 20.ALLEN 1600 30TURNER 1500 30 has selected 9 rows. 5. The subquery of Oracle needs to pay attention to a. You can't use subquery in group by clause. Example: select avg (sal) from emp group by (select deptno from emp); SQL > select avg (sal) from emp group by (select deptno from emp); select avg (sal) from emp group by (select deptno from emp) error in line 1: ORA-22818: subquery expression b is not allowed here, in TOP-N analysis problems, subquery sorting example: displays the top five employees with the highest salary in the employee information table. SQL > select rownum, empno, ename, sal from (select * from emp order by sal desc) where rownum select ename, job from emp where job = (select job from emp where ename = 'Ruby'); unselected rows if the fruit query returns a null value, then the main query will not query any results. D. Example of null value problem of multi-row subquery: select * from emp where empno not in (select mgr from emp); SQL > select mgr from emp; MGR 7566 null. 7839 7566 7698 15 rows have been selected. SQL > select * from emp where empno not in (select mgr from emp); if a null value occurs in an unselected multirow subquery, the main query will not find any results. SQL > select * from emp where empno not in (select mgr from emp where mgr is not null) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7844 TURNER SALESMAN 7698 08-September-81 1500 0 30 7951 EASON ANALYST 7566 01-December-17 3000 20. 7934 MILLER CLERK 7782 23-January-82 1300 10 7900 JAMES CLERK 7698 03-December-81 950 30 9 lines have been selected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.