Oracle Learning Series 3

Oracle Learning Series 3************************************************************************************ Multi-table query: 1, SQL1999 syntax support for multi-table queries 2, the use of grouping statistics and statistical functions 3, sub-query, and combined with multi-table query, grouping statistics to do complex query 4, database update operation 5, transaction processing and database deadlock ********************************** Multi-Table query: syntax: SELECT {DISTINCT} * | col1 alias 1 col2 alias 2 ... from TAB1 alias 1, tab2 alias 2, TAB3 alias 3, ... {Where Condition S} {ORDER BY col1 ASC | DESC, col2 ASC | DESC, ...}   Simultaneously query the EMP table and Dept table: Select *from emp, dept; Generate Cartesian product Join where statement: Select *from emp e,dept dwhere e.deptno = D.deptno; Ask for employee's number, name, department number, department name, department location: select E.empno, E.ena Me, D.deptno, D.dname, D.locfrom emp E, dept dwhere E.deptno=d.deptno; Ask for the name of each employee, the name of the job, the employee's direct superior leader: Select E.ename, E.job , M.ename, D.dnamefrom emp E, EMP m, dept dwhere e.mgr = m.empno and e.deptno=d.deptno; ask for each employee's name, salary, department name, salary at the company level, and its leadership Name and salary of the company's rank: select E.ename, E.sal, D.dname, S.grade, M.ename, M.sal, Ms.gradefrom emp E, Dept D, Salgrade S, emp m, Salg Rade Mswhere E.deptno=d.deptno and (E.sal betweenS.losal and S.hisal) and E.mgr=m.empno and (M.sal between Ms.losal and Ms.hisal); further: show wage levels as follows: 1: Fifth salary 2: Fourth salary 3: third-class work 4: Second-rate salary 5: The first-rate salary at this time must use DECODE () function: Select E.ename, E.sal, D.dname, DECODE (S.grade, 1, ' Fifth wages ', 2, ' fourth wages ', 3, ' third wages ', 4, ' Second-rate wages ', 1, ' first-rate wages ', M.ename,m.sal,decode (Ms.grade, 1, ' Fifth wages ', 2, ' fourth wages ', 3, ' third-rate wages ', 4, ' second-rate wages ', 1, ' first-rate wages '), from EMP E,     Dept D, Salgrade S, emp m, Salgrade mswhere E.deptno=d.deptno and (E.sal between S.losal and S.hisal) and E.mgr=m.empno and (M.sal between Ms.losal and Ms.hisal); ************************************************************************* Left, right connect < focus > (+) =-right connection, = (+)-left connection, default select E.empno, E.ename, D.deptno, d.dname, D.locfrom em P E, Dept dwhere E.deptno (+) =d.deptno; Indicates that the Dept table shall prevail. Right connect ************************************************************************************sql:1999 syntax support < understanding for SQL > Cross Join: < produces cartesian product >select * from EMP corss join dept; Natural Join (natural join):< automatically matches associated field >select * fro M emP Natural Join Dept; The Using clause:< directly specifies the associated action column >select *from EMP e join Dept D USING (DEPTNO) where Deptno=30;on clause:< user-defined connection conditions >select * From the EMP e join Dept D on (E.deptno=d.deptno) where e.deptno=30; the left join and right join: Select E.ename, D.deptno, D . dname, D.locfrom EMP E right outer JOIN Dept Don (E.DEPTNO=D.DEPTNO); ************************************************* Group functions and Grouping statistics:< Focus >count ()://Find out All Records max ()://Find out a set of Maximum min ()://min AVG ()://  Average sum ()://Sum count () function: SELECT COUNT (EMP) from EMP; Query how many rows of records Max (), Min () function: Find the minimum wage for all employees select MAX (sal), Min (sal), sum (SAL) from EMP, group statistic <group By>: syntax: select { DISTINCT} * | col1 alias 1 col2 alias 2 ... from TAB1 alias 1, tab2 alias 2, TAB3 alias 3, ... {Where Condition S} {GROUP BY group condition} {ORDER BY col1 ASC | DESC, col2 ASC | DESC, ...} The number of employees in each department can be determined according to the department number: select Deptno, Count (empno) from Empgroup by Deptno; average salary per department: Select Deptno, Avg (SAL) from EMP Group by Deptno;---------------------------------------------------------------------------------Select Deptno, Count (empno) from EMP;//Error: Not a single group of grouping functions/** if the program uses a grouping function, the condition is as follows  ; 1. A group by IS present in the program, and a grouping condition is specified so that the grouping condition can be queried together 2. If you don't use grouping, you can only use the Grouping function 3. Fields outside the grouping function and grouping criteria (group by) cannot appear when using grouping functions |  */    | |^select Deptno, Empno, Count (empno)//error:empno is not a group by expression from the empgroup by Deptno; Groups by department and displays the name of the department and the number of employees per department: Selec T D.dname, Count (e.empno) from Dept D, emp ewhere D.deptno = E.deptnogroup by d.dname; requires that the department number and average salary of the average wage greater than 2000 be displayed: Error:sel  ECT deptno, AVG (SAL) from Empwhere avg (SAL) >2000//This is not allowed to use grouping functions Group by DEPTNO; The/** grouping function can only be used in groups, not in the where statement, and if you now assume that you want to specify a grouping condition, you can only pass the second command: Having, at this time, the SQL syntax format: SELECT {DISTINCT} * | col1 alias 1 col2 alias 2 ... from TAB1 alias 1, tab2 alias 2, TAB3 alias 3, ... {Where Condition S} {GROUP BY group condition {having group condition}} {ORDER BY col1 ASC | DESC, col2 ASC | DESC, ...} ; */correct:select deptno, Avg (SAL) from Empgroup to Deptno having avg (SAL) >2000; Displays the non-salesperson's job name and the sum of the monthly wages of the employee who is engaged in the uniform work, And to meet the total monthly wage of employees who work in the same job is greater than $5000, the output is sorted in ascending order of monthly wages:/** grouping Simple principle: 1, as long as there is duplicate content on a columnIt is possible to consider grouping 2, grouping functions can be nested, but nested use cannot appear after the group by field: Ex: Find the department with the highest average wage: Error:select deptno, Max (avg. sal), or not a single group grouping function from Empgroup by Deptno;correct:select Max (avg. sal) from Empgroup by Deptno;--------------------------------------------- -----------------------------------Analysis: 1, Show all non-sales personnel:job<> ' salesman ' select * from emp where job <> ' salesman '; 2, grouped by work, at the same time to find the sum of wages: Select Job,sum (SAL) from the EMP where job<> ' salesman ' group by job; 3, limit the grouping conditions, the sum of wages is greater than 500:select  Job,sum (SAL) from the EMP where job<> ' salesman ' GROUP by job has sum (SAL) >5000; 4, sorted in ascending order: Select Job,sum (SAL) sufrom EMP where job<> ' salesman ' GROUP by job has sum (SAL) >5000 order by Su; */************************************************************************************ subquery: Within one query you can also include another query: Category: Single-row subquery: Returned knot The result is one of the contents of a column (highest probability) single-row subquery: Returns multiple columns, possibly a full record multiline subquery: Returns multiple record formats: SELECT {DISTINCT} * | col1 alias 1 col2 alias 2 ... from TAB1 alias 1, TAB2 alias 2, (select {DISTINCT} * | col1 alias 1 col2 alias 2 ... from TAB1 alias 1, TAB2 Alias 2, Tab3 alias 3, ... {Where Condition S} {GROUP BY group condition {having group condition}} {ORDER BY col1 ASC | DESC, col2 ASC | DESC, ...} ; ) Alias X tab3 alias 3, ... {Where Condition S (select {DISTINCT} * | col1 alias 1 col2 alias 2 ... from TAB1 alias 1, tab2 alias 2, TAB3 alias 3, ... {Where Condition S} {GROUP BY group condition {having group condition}} {ORDER BY col1 ASC | DESC, col2 ASC | DESC, ...} ; )}{group by grouping condition {having grouping condition}} {ORDER by col1 ASC | DESC, col2 ASC | DESC, ...}   ; Ask for information about all employees with a higher than 7654 salary: Analysis: 1, 7654 What is the salary of the employee: Select Sal from EMP where empno=7654; 2, as long as the salary of the employee is more than 7654 of the other wage, then it is eligible: SELECT * FR  Om empwhere sal > (select sal from EMP where empno=7654; Sub-query statement), asking for a higher salary than 7654, and 7788 of all employee information that is engaged in the same job: Query the wages of 7654 employees: Select Sal from emp where empno=7654; do the same job as 7788: Select Job from emp where empno = 7788; Comprehensive lookup: SELECT * from Empwhere sal > (select sal from EMP where empno=7654) and job = (Sele CT job from emp where empno = 7788); Ask for the lowest wage employee's name, job, Salary: Minimum wage: select min (sal) from EMP; query all: SELECT * from Empwhere SA L = (select min (sal) FROm EMP); Request query: Department Name, department employee number, department average salary, department minimum income employee's name: 1, find the number of employees per department, average wage select Deptno,count (empno), avg (SAL) from Empgroup by DEPTNO; 2, identify the name of the department: Select D.dname, ED.C, ed.a from Dept D, (select Deptno,count (empno), avg (SAL) from Empgroup by DEPTN o) edwhere D.deptno=ed.deptno; 3, the name of the employee who obtained the lowest income: Select D.dname, ED.C, ED.A, E.enamefrom dept D, (select Deptno,count (em pno), avg (Sal), Min (sal) Minfrom Empgroup by Deptno) Ed, emp Ewhere D.deptno=ed.deptno and E.sal =ed.min; If there are two minimum wage employees in a department, the script will have an error------------------------------------------------------the three operators in the subquery: In, any, all find each part Door minimum Wage Employee information: SELECT * from EMP where Sal in (//Specify query range Select min (sal) from EMP Group by DEPTNO); =any: Same as in function s  Elect * from EMP where Sal =any (//Specify query range Select min (sal) from EMP Group by DEPTNO); >any: Larger than the smallest value on the inside select * From EMP where Sal >any (//Specify query range Select min (sal) from EMP Group by DEPTNO); <any: Smaller than the maximum value select * Fro M EMP where Sal <any (//Specify query range SelecT min (sal) from EMP Group by DEPTNO); ==========================>all: Larger than maximum select * from emp where Sal >  All (//Specify the query range select min (sal) from EMP Group by DEPTNO); <all: Smaller than the maximum value select * from emp where Sal <all (  Specify Query Scope Select min (sal) from EMP Group by DEPTNO); /** for subqueries, you can also make multiple-column subqueries, one in which multiple queries are returned in a subquery select *from empwhere (Sal, NVL (comm,-1)) in (select SA,NVL (Comm,-1) from EMP WH    ere deptno=20); */

Oracle Learning Series 3