Multi-Table QueryEquivalent connection (equijoin) Select Ename,empno,sal,emp.deptno from emp,dept where dept.deptno=emp.deptno; non-equivalent connection (non-equijoin) Select Ename, Empno,grade from Emp,salgrade where Sal>losal and SalSelect E.empno,e.ename,m.empno,ename from emp e,emp m where m.mgr=e.empno;
Left outer join (empno,ename,dname) Select-Emp,dept where Emp.deptno=dept.deptno (+); Select Empno,ename,dname from EMP left OUTER join on Emp.deptno=dept.deptno, right outer join (starboard outer join)Select Empno,ename,dname from emp,dept where Emp.deptno(+)=dept.deptno;
Select Empno,ename,dname from EMP right outer join on EMP.DEPTNO=DEPT.DEPTNO;Full outer join (Outer join) Selece empno,ename,dname the from EMP fully Outer join dept on Emp.deptno=dept.deptno; PS: Left JOIN to the left table, the null value of the right table is not considered, the right connection is connected 2 24 4 full Connection 12 23 4 45 6 left connection 12 23 4 45 Right Connection 2 24 4 6 set operation (the best table structure is the same, at least requires the same number of fields on both sides of the Union) Unino: Unions, All content is queried, repeated once Unino all: The set, all the contents are displayed, including duplicate intersect: intersection: Show only duplicate minus: difference set, Only show that the other party does not have (in order) create a 20-door employee Information table: Create Teble EMP20 as SELECT * from EMP where Deptno=20;select * from EMP Union SELECT * FROM Emp20;select * FROM EMP UNION ALL SELECT * from EMP20;SELECT * FROM emp intersect select * from EMP20;
SELECT * from emp minus select * from Emp20;
Sub-querysingle-line subquerySELECT * from emp where sal > (select sal from emp where empno=7566);(subquery is NULL then the main query will not return any results)multi-row subqueriesSelect ename, sal from EMP where Sal>any (select AVG (SAL) from EMP Gorup by Deptno), higher than three divisions of any average levelSelect Ename, sal from EMP where Sal>all (select AVG (SAL) from EMP Gorup by Deptno); higher than the average level of three departments
Select Ename,job from emp where job in (the Select Job from emp where ename= ' MARTIN ' or ename= ' SMITH '); TOPN Query SELECT * FROM emp ORDER BY sal Desc where rownum<=5; (the value of RowNum is 2 is the 2nd row, here is the first five rows returned) a paging query select * FROM (select RowNum No, E.* from (SELECT * from emp ORDER BY sal Desc) e where rownum<5) where no>=3;SELECT * FROM (select RowNum no,e.* from (SELECT * from emp ORDER BY sal Desc) e) where no>=3 and no<=5;
exists ( similar to in) SELECT * from t1 where exists (select null from T2 where y=x);Performance differences for in and exists: if the subquery results in a relatively small number of result set records, the main query in the larger table and index should be used in, conversely, if the outer layer of ink query records less, sub-query expression, and the index with exists.In fact, we distinguish in and exists is mainly caused by the change of the driving sequence (this is the key to performance change), if it is exists, then the other layer table is the driver table, first accessed, if it is in, then the subquery will be executed first, so we will be the driver table of the fast return as the target, Then the relationship between the index and the result set will be taken into account.In addition, NULL is not processed, such as:Select 1 from dual where null in (0,1,2,null) is emptyExercise: 1. List the number of employees per department in the employee table, and department noselect Deptno,count (*) from EMP Group by DEPTNO2. Lists the number of employees in each department in the employee table (the number of employees must be greater than 3) and the department name Select D.*, Ed.cou from Dept D, (select Deptno,count (*) cou from EMP Group by DEPTNO have Count (*) >3) Ed where ed.deptno=d.deptno;3 Find employees who pay more than JONES. SELECT * from emp where sal> (select Sal from emp where ename= ' JONES '); 4, lists the names of all employees and their superior names select E1.ename Lower,e2.ename Upper from EMP e1,emp E2 where e1.mgr=e2.empno (+); 5. To find the two positions with the highest average wage, select * FROM (select Job,avg (SAL) From EMP GROUP BY Job ORDER by AVG (SAL) desc) where rownum<3;6. Name of the employee who is not in department 20 and is higher than the salary of any person in department 20, department name Select Ename,dname F Rom emp e,dept d where E.deptno!=20 and E.deptno=d.deptno and Sal >all (select Sal from EMP where deptno=20);Select Ename,dname from emp e,dept D where E.deptno!=20 and E.deptno=d.deptno and Sal > (select Max (SAL) from EMP W Here deptno=20);
7. Job categories with average wage greater than 2000Select Job from EMP GROUP BY job have avg (SAL) >2000;8. Get the department number, department name, department location for the department with the lowest total monthly payrollSELECT * FROM dept where deptno= (select E.deptno from (select Deptno,sum (SAL) from EMP Group by Deptno ORDER by sum (SAL)) E where Rownum=1) 9. Get the department number for the average salary level of 4 (Salary scale table Salgrade) Select E.deptno from Salgrade g, (select Deptno,avg (SAL) avgsal from EMP Group by Deptno) e where g.grade=4 and e.avgsal between G.losal and g.hisal;10. Find out the income (salary plus bonus), lower than the upper level of the employee number, employee name, employee income select E. ENAME,E.ENAME,E.SAL+NVL (e.comm,0) from EMP e,emp m where E.mgr=m.empno and (E.SAL+NVL (e.comm,0)) > (M.SAL+NVL (M.comm), 0); 11. Find employee names, department names, department positions for employees whose salary level is not level 4 select E.ename,d.dname,d.loc from emp e,dept d,salgrade g where e.deptno=d.deptno and g.grade=4 and e.sal not between G.losal and g.hisal;12. Find out the average wage for employees like ' Martin ' or ' Smith ' select AVG (SAL) from EMP where Job in (the Select Job from emp where ename= ' MARTIN ' or ename= ' SMITH ');
Oracle learns notes from scratch two
