Practice I. Name, salary, department name, number of departments of employees who have paid more than the salaries of all employees working in department 30Think steps: 1, determine the data tables you need to use I. EMP: employee's name, salary Second, Dept: Department Table: Department name &NBS P EMP: Number of departments: Need to use statistic Field 2, determine known associated fields associated fields: emp.deptno=dept.deptno 1, Find out the salary of all employees in department 30 work select sal from EMP where deptno=30; ---the queried data is a multiline single row of data, Need to be larger than all the data in this, need ALL 2, find all personnel information greater than the above conditions select ename, Salfrom empwhere sal >all (select Sal from EMP where DEP tno=30) ---key:>all where clauses nested 3, display department name---need to correlate Dept Table Select E.ename, E.sal, D.dnamefrom EMP e,dept dwhere e.sal >all (select Sal from emp where deptno=30) and e.dept no=d.deptno; &NBSP ; -----Note that in the comma after the From, you cannot add more commas 4, Statistics Department number: Select E.ename, E.sal, D.dname, temp.countfrom emp e,dept D, (select Deptno, Count (empno) count from EMP Group by DEPTNO) TempwhereE.sal >all (select Sal from emp where deptno=30) and E.deptno=d.deptno and e.deptno=temp.deptno; ----Note two: If you need statistics, you need to group But when using grouping, only grouped fields and statistical functions can appear in the SELECT clause, not other fields we need, so we use the FROM clause to operate , it is now grouped as a temporary table and then associated operations Note that there are several associated actions that require several field associations to eliminate the Cartesian product
Exercise two: List all employees and department names, department numbers, and leadership names that are in the same job as ' Scott '. Determine the data table to use: 1, EMP: Determine work, employee name, leader name needs to be displayed according to the code 2, Dept: determine the name of the Department & nbsp; 3, number of departments determine known associated fields: emp.deptno=dept.deptno& nbsp. List all employees and department names, department numbers and leadership names that are in the same job as ' Scott '. determine the data table to use: 1, EMP: Determine work, employee name, leader name needs to be displayed according to the code      2, Dept: determine the name of the department 3, number of departments identify the known associated fields: emp.deptno=dept.deptno Emp.mgr=emp.empno---its own relevance is very important 1. Try to find out the leadership of the employee, find SCOTT's leader select Mgr from EMP where ename= ' SCOTT '; Select ename from emp where empno= (select Mgr from emp where ename= ' Scott '); 2, find colleagues who work with Scott Select job from EMP whe Re ename = ' Scott '; select ename from emp where JOB = (SELECT job from emp where ename = ' Scott '); select Ena Me mgrname from EMP where empno= (SelecT MGR from emp where ename = ' SCOTT '); 3, department name, need to correlate another table select E.empno, E.ename, d.dnamefrom emp e,dept dwhere JOB = (sel ECT job from emp where ename = ' SCOTT ') and e.deptno=d.deptno; 4. Number of departments, The number of departments required to group data, after grouping statistics select count (empno) from the EMP group by DEPTNO; ---to group it as a new function to correlate select e.empno, E.ename, D.dname, Temp.count, ename mgrnamefrom EMP e,dept D, (SELECT Deptno,count (empno) count from EMP Group by deptno) tempwhere job = (select Job from emp where ename = ' SCOTT ') & Nbsp; and E.deptno=d.deptno and e.deptno= temp.deptno; ----In the Select value, you have to take out the fields you need to use 5, show leader, associate with yourself Select E.empno, E.ename, D.dname, Temp.count, c.ename mgrnamefrom emp e,dept D, (select Deptno,count (empno) count from EMP Group by Deptno) temp,emp Cwhere e.job = (select JOB from emp where ename = ' SCOTT ') and E.deptno=d.Deptno and e.deptno=temp.deptno and e.mgr=c.empno; 6, remove duplicate information: SELECT e.empno, E.ename, D.dname, Temp.count, C.ename mgrnamefrom emp e,dept D, (select Deptno,count (empno) count from EMP Group by Deptno) temp,emp cwhere e.job = (Selec T job from emp where ename = ' SCOTT ') and e.deptno=d.deptno and E.deptno=temp.deptno and E.mgr=c.empno and e.ename!= ' SCOTT ';
Iii. List of all employees whose salary is more than ' SMITH ' or ' ALLEN ', number, name, department name, leader name, department number, average wage, maximum wage, minimum wage Determine the data table to use: 1, EM P: Determine employee number, employee name, leader name to be displayed according to code   2, Dept: identify the name of the department  3, number of departments, according to the group after the calculation determine the known associated fields: emp.deptno=dept.deptno emp.mgr=emp.empno---Self-correlation is important to show the leader's name count, Max, Min, avg ----used to calculate the highest, lowest, average wage Exercise III: List the number, name, department name, and the names of all employees who pay more than ' SMITH ' or ' ALLEN ' Leadership name, department number, average wage, maximum wage, minimum wage determine the data table to use:                 1, EMP: Determine employee number, Employee name and leader name need to be displayed according to the code                   2, Dept: identifying the name of the department        3, number of departments, according to the group after the calculation determine the known associated fields: emp.deptno=dept.deptno emp.mgr=emp.empno---Self-correlation is important to show the leader's name count, Max, Min, avg ----used to calculate the highest, lowest, average wage 1, find out the number of all employees whose salary is more than ' SMITH ' or ' ALLEN ', select sal from emp where ename= ' SMITH ' or ename = ' ALLEN '; ---- >any is greater than the minimum value in them select empno, ename, Salfrom empwhere Sal >any (select Sal from emp where ename= ' SMITH ' or ename = ' ALLEN '),  2, show department name, need to correlate Dept Table select E.empno, E.en Ame, E.sal, D.dnamefrom emp E, dept dwhere Sal >any (select Sal from emp where ename= ' SMITH ' or ename = ' ALLEN ') &n Bsp; and e.deptno=d.deptno; 3, calculate the number of departments, need to be grouped by department, showing the calculation of the maximum wage, minimum wage, average wage select Deptno, Count (empno) from the EMP Group by Deptno; select E.empno, E.ename, E.sal, D.dname, Temp.count, temp. AVG, temp. MAX, temp. Minfrom EMP E, Dept D, (select Deptno, Count (empno) count, AVG (SAL) Avg,max (SAL) max,min (SAL) MIN from EMP Group by Deptno ) Tempwhere sal >any (select Sal from emp where ename= ' SMITH ' or ename = ' ALLEN ') and E.deptno=d. Deptno and e.deptno=temp.deptno; 4. Show leader name select E.empno, E.ename, E.sal, D.dname, C.ename mgrname,temp.count, temp. AVG, temp. MAX, temp. Minfrom EMP E, Dept D, (select Deptno, Count (empno) count, AVG (SAL) Avg,max (SAL) max,min (SAL) MIN from EMP Group by Deptno Temp, emp cwhere e.sal >any (select Sal from emp where ename= ' SMITH ' or ename = ' ALLEN ') and E. Deptno=d.deptno and E.deptno=temp.deptno and e.mgr=c.empno; 5. Remove duplicate data and check select E.empno, E.ename, E.sal, D.dname, C.ename Mgrname,temP.count, temp. AVG, temp. MAX, temp. Minfrom EMP E, Dept D, (select Deptno, Count (empno) count, AVG (SAL) Avg,max (SAL) max,min (SAL) MIN from EMP Group by Deptno Temp, EMP c ----This side is statistical data, is not directly placed in the SELECT statement, otherwise it will cause other types of fields not to display where E.sal >any (select Sal from EMP where Ename= ' SMITH ' or ename = ' ALLEN ') and E.deptno=d.deptno and e.deptno= Temp.deptno and E.mgr=c.empno (+) ---in order to display information about employees without leadership and E.ename not in (' SMITH ', ' ALLEN '); ---to exclude data already involved in the title (remove duplicates)
Exercise Four: List the number, name, department name, department location, number of departments for all employees whose employment date is earlier than their direct ancestor. determine the data table to use: &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;1, EMP: Determine employee number, employee name, leader name to be displayed according to the code, and employment date &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;2, Dept: identify the department's name, department location &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;3, number of departments, Calculated based on grouping determining known associated fields: emp.deptno= Dept.deptno emp.mgr=emp.empno---Self-correlation is important to show the name of the leader count, Max, Min, avg ----used to calculate the highest, lowest, average wage Exercise four: List the number, name, department name, department location, number of departments, determine the data table to use for all employees whose employment date is earlier than their immediate superiors: &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;1, EMP: Determine employee number, employee name, The name of the leader needs to be displayed according to the code and the employment date &nbsP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;2, Dept: determine the name of the department, department location &NBSP;3, number of departments, according to the group after the calculation determine the known associated fields: It is important that emp.deptno=dept.deptno emp.mgr=emp.empno---self-correlation, Used to show the leader's name count, Max, Min, avg ----used to calculate the highest, lowest, average wage 1, Calculation hiredate Association of employment Dates you can use relationships directly: Emp.mgr=emp.empno select e.empno, E.ename, E.hiredate staff time, c.hiredate boss time from emp e,emp cwhere E.hiredate<c.hiredate and e.mgr= C.EMPNO;&NBSP;&NBSP;2, show department name and department position, need to correlate Dept Table Select E.empno, E.ename, e.hiredate staff time, c.hiredate boss time, D.dname, D.locfrom EMP e,emp C, dept dwhere E.hiredate<c.hiredate and e.mgr=c.empno and e.dePTNO=D.DEPTNO;&NBSP;3, number of computing departments, need group by to group select Deptno, Count (empno) Countfrom Empgroup by deptno; Select E.empno, E.ename, e.hiredate staff time, c.hiredate boss time, D.dname, D.loc, temp.countfrom EMP e,emp c, Dept D, (select DEP TNO, Count (empno) Countfrom Empgroup by Deptno) Tempwhere E.hiredate<c.hiredate and e.mgr= C.empno and E.deptno=d.deptno and e.deptno=temp.deptno; Exercise five: List all ' clerk ' names and department names, department numbers, salary levels determine the data table to use: &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;1, EMP: Determine employee name and work &NBSP;2, Dept: determine the name of the department 3, EMP: Number of departments, according to the group after the calculation 4, Salgrade: wage level grade determine known associated fields: emp.deptno=dept.deptno &Nbsp;emp.mgr=emp.empno---Self-correlation is important to show the leader's name 1, to find out all the job's employee information for ' clerk ' select ename, Salfrom empwhere job= ' clerk '; &NBSP;2, display department name, Associated dept Table Select E.ename, E.sal, D.dnamefrom emp E, dept dwhere job= ' clerk ' and E.DEPTNO=D.DEPTNO;&NBSP;3, number of computing departments, using GROUP by group Select Deptno, Count (empno) Countfrom Empgroup by Deptno select E.ename, E.sal, D.dname, Temp.countfrom emp E, Dept D, (select Deptno, Count (empno) Countfrom Empgroup by Deptno) tempwhe Re job= ' clerk ' and E.deptno=d.deptno and E.deptno=temp.deptno; 4, calculate salary level check Scott's salary levels? Select S.grade, E.ename,e.salfrom salgrade S, emp ewhere e.sal between S.losal and s.hisal; Select E.ename, E.sal, D.dname, Temp.count, S.gradefrom emp E, Dept D, (select Deptno, Count (empno) Countfrom Empgroup by DEPTNO) Temp, salgrade swhere job= ' clerk ' and E.deptno=d.deptno and e.deptno=temp.deptno and e.sal between S.losal and s.hisal;
Exercises for Oracle complex queries
