Oracle Complex queries and summaries

One, complex query

1. List all department numbers and names of at least one employee and count the average wage, minimum wage and maximum wage of these departments.

1. Determine the required data sheet:

• EMP Table: Can find out the number of employees;
• Dept Table: Department name;
• EMP Table: Statistical information;

2. Determine the known associated fields:

• Emp.deptno=dept.deptno;

The first step: Find out the department number of at least one employee

SELECT Deptno,count (empno) from Empgroup by Deptnohaving COUNT (empno) >1;

The second step: Find the department name, definitely use the department table, because now the amount of data is small, so you can connect the previous EMP table and Dept table two, unified use of multi-field grouping method query;

SELECT D.deptno,d.dname,count (e.empno) from emp e,dept dwhere e.deptno=d.deptno (+) GROUP by d.deptno,d.dnamehaving COUNT (e.empno) >1;

The third step: still need to continue to statistics

SELECT D.deptno,d.dname,count (E.empno), AVG (SAL), MIN (SAL), MAX (SAL) from the EMP e,dept dwhere e.deptno=d.deptno (+) GROUP by D.deptno,d.dnamehaving COUNT (e.empno) >1;

2. List the number, name, department name, and leader name of all employees who pay more than "SMITH" or "ALLEN".

1. Determine the required data sheet:

• EMP Table: Query the "SMITH" or "ALLEN" salary;
• EMP Table: The final display needs number, name;
• EMP Table: The name of the leader, its own association;
• Dept Table: Department name;

2. Determine the known associated fields:

• Employees and leaders: Emp.mgr=memp.empno;
• Employees and departments: Emp.deptno=dept.deptno;

The first step: find out the "SMITH" or "ALLEN" salary

SELECT sal from EMP WHERE ename in (' SMITH ', ' ALLEN ');

The second step : The above query returns a multi-row single record, according to the requirements of the subquery in the WHERE clause write appropriate, so this time the above query as a subquery appears, continue to query the number of employees who meet this requirement, name.

Select E.empno,e.enamefrom emp ewhere e.sal>all (    select Sal from    emp    WHERE ename in (' SMITH ', ' ALLEN '));

The third step: to find out the name of the department, introduce the department table, and increase the elimination of Cartesian product conditions

Select E.empno,e.ename,d.dnamefrom emp e,dept dwhere e.sal>all (    select Sal from    emp    WHERE ename in (' SMITH ', ' ALLEN ') and    E.deptno=d.deptno;

Fourth Step: The information of the leader needs to be associated with the EMP table itself

Select E.empno,e.ename,d.dname,m.enamefrom emp e,dept d,emp mwhere e.sal>all (    select Sal from    EMP    WHERE ename in (' SMITH ', ' ALLEN ')) and    E.deptno=d.deptno and    e.mgr=m.empno (+);

3, the number of all employees, names and their direct superiors of the number, name, the results are displayed in descending order of leadership annual wages.

1. Determine the required data sheet:

• EMP Table: Employee's number, name;
• EMP Table: Leader's number, name, calculation of annual salary;

2, determine the known correlation field: emp.mgr=memp.empno;

SELECT E.empno,e.ename,m.empno,m.ename, (M.SAL+NVL (m.comm,0)) *12 incomefrom emp e,emp mwhere e.mgr=m.empno (+) ORDER by Income DESC;

4. List the number, name, department name, department position, department number of all employees whose employment date is earlier than their direct superiors.

1. Determine the required data sheet:

• EMP Table: Employee's number, name;
• EMP Table: Find out the working date of the leader;
• Dept Table: Department name, location;
• EMP Table: Number of statistics departments;

2. Determine the known associated fields:

• Employees and departments: Emp.deptno=dept.deptno;
• Employees and leaders: Emp.mgr=memp.empno;

First step: list the number and name of all employees whose employment date is earlier than their direct superiors-The associated EMP table.

SELECT e.empno,e.enamefrom emp e,emp mwhere e.mgr=m.empno (+) and e.hiredate<m.hiredate;

The second step: Join the Department information, continue to introduce the Department table

SELECT e.empno,e.ename,d.dname,d.locfrom emp e,emp m,dept dwhere e.mgr=m.empno (+) and E.hiredate<m.hiredateand E.deptno=d.deptno;

Additional questions, at this time Cartesian product: EMP Table 14 * EMP Table 14 * Dept Table 4;

The third step: statistics of the number of departments, at this time due to the use of statistical functions, and the above query can no longer directly appear in the statistical function, so the use of sub-query completion

Select E.empno,e.ename,d.dname,d.loc,temp.countfrom emp e,emp m,dept D, (    select Deptno dno,count (empno) COUNT    From EMP    GROUP by Deptno) Tempwhere E.mgr=m.empno (+) and e.hiredate<m.hiredate and    E.deptno=d.deptno    and E.deptno=temp.dno;

Statistical information is required when the query is displayed, but it cannot be queried directly using a statistical function, which is counted after the FROM clause by a subquery.

5. List the department name and employee information (quantity, average salary) of these departments, and list the departments that have no employees.

1. Determine the required data sheet:

• Dept Table: Information of the Department;
• EMP Table: Find out all the statistical information;

2, determine the known correlation field: Emp.deptno=dept.deptno;

SELECT D.deptno,d.dname,d.loc,count (E.empno), AVG (e.sal) from the EMP e,dept dwhere e.deptno (+) =d.deptnogroup by D.deptno, D.dname,d.loc;

6. List the names of all "clerk" (clerks) and their departments, the number of departments, and the salary level.

1. Determine the required data sheet:

• EMP Table: Find the name of the clerk;
• Dept Table: Department name;
• EMP Table: Statistics to find out the number of departments;
• Salgrade table: Check the salary level;

2. Determine the known associated fields:

• EMP Table and Dept table: Emp.deptno=dept.deptno;
• EMP table and Salgrade table: Emp.sal between Salgrade.losal and Salgrade.hisal;

First step: find the names of all the clerks

SELECT e.enamefrom emp ewhere job= ' clerk ';

Step Two: Find department information, Introduce dept table, and increase the elimination of Cartesian product conditions

SELECT e.ename,d.dnamefrom emp e,dept dwhere job= ' clerk ' and E.deptno=d.deptno;

The third step: the number of departments need additional statistics, but the query in this program is not possible to continue to use the count () function, so write subquery statistics

Select E.ename,d.dname,temp.countfrom emp e,dept D, (    select Deptno dno,count (empno) COUNT from    emp    GROUP by DEPTNO) tempwhere e.job= ' clerk ' and E.deptno=d.deptno and    D.deptno=temp.dno;

Fourth: Employee's salary level, continue to introduce Salgrade table

Select E.ename,d.dname,temp.count,s.gradefrom emp e,dept D, (    select Deptno dno,count (empno) count from    EMP    GROUP by Deptno) temp,salgrade swhere e.job= ' clerk ' and E.deptno=d.deptno and D.deptno=temp.dno and E.sal    Between S.losal and S.hisal;

7. List all kinds of jobs with minimum salary of more than 1500 and the number of employees and the name, location and average salary of the department in which they work.

1. Determine the required data sheet:

• EMP Table: The minimum salary of more than 1500 of the work must be calculated using the EMP table statistics, and can find out the number of employees;
• Dept: Find out the department information of some employees;
• EMP Table: Statistics to find out the average salary of the department;

2, determine the known correlation field: Emp.deptno=dept.deptno;

First step: use the EMP table to group jobs, statistics minimum wage (having) and number of people

SELECT E.job,count (e.empno) from emp egroup by e.jobhaving MIN (e.sal) >1500;

The second step: to find out the employee's department information, but the above query can be associated with the Dept table?

There is no correlation between the above query and the Dept table, then if there is no associated field, there will be a Cartesian product, but the multi-table query must eliminate the Cartesian product, so must contact;

• The above query can be associated with the job field of the EMP table;
• The Dept table to be introduced can also be associated with the Deptno field of the EMP table;

Select Temp.job,temp.count,d.dname,e.enamefrom Dept D, (    select E.job job,count (e.empno) count from    EMP e    GROUP by E.job have    MIN (e.sal) >1500) temp,    emp ewhere e.deptno=d.deptno and E.job=temp.job;

The third step: find out a department's average salary, using the EMP table in the subquery statistics

Select Temp.job,temp.count,d.dname,e.ename,res.avgfrom Dept D, (    select E.job job,count (e.empno) Count    from EMP e    GROUP by E.job have    MIN (e.sal) >1500) temp,    emp E, (    SELECT deptno dno,avg (sal) AVG    From EMP    GROUP by Deptno) Reswhere E.deptno=d.deptno and E.job=temp.job and    E.deptno=res.dno;

The reason why this topic is so complex, the purpose is to train everyone to find the ability to relate to fields, but this topic does not have any meaning, know on the line.

8. List the employee's name, base salary, employment date, department name, and the department number assumed not to know the sales department in the department "sales".

1. Determine the required data sheet:

• EMP Table: Employee name, base salary, employment date;
• Dept Table: Find the department number of sales department, department name;

2, determine the known correlation field: Emp.deptno=dept.deptno;

SELECT e.ename,e.sal,e.hiredate,d.dnamefrom emp e,dept dwhere e.deptno=d.deptnoand d.dname= ' SALES ';

9. List all employees who pay more than the average salary of the company, the department, the superior leader, the company's salary level.

1. Determine the required data sheet:

• EMP Table: Can find out the average salary of the company;
• EMP Table: employee's information;
• Dept Table: Information of the Department;
• EMP Table: Information of the leader;
• Salgrade table: Salary grade;

2. Determine the known associated fields:

• Employees and departments: Emp.deptno=dept.deptno;
• Employees and leaders: Emp.mgr=memp.empno;
• Employees and wage levels: Emp.sal between Salgrade.losal and Salgrade.hisal;

The first step: to find out the company's average salary

SELECT AVG (SAL) from EMP;

The second step: put the above sub-query in the WHERE clause, as a query condition, to find the employee information that satisfies this condition.

Select E.empno,e.ename,e.job,e.salfrom emp ewhere e.sal> (    select AVG (SAL) from EMP);

Step Three: find the name of the department

Select E.empno,e.ename,e.job,e.sal,d.dname,d.locfrom emp e,dept dwhere e.sal> (    select AVG (SAL) from EMP)    and E.deptno=d.deptno;

Fourth Step: Find the information of the leader

Select E.empno,e.ename,e.job,e.sal,d.dname,d.loc,m.enamefrom emp e,dept d,emp mwhere e.sal> (    select AVG (SAL) From EMP) and    E.deptno=d.deptno and    e.mgr=m.empno (+);

Fifth Step: Find the salary level

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc,m.ename,s.gradefrom emp e,dept d,emp m,salgrade SWHERE e.sal> (    SELECT AVG (sal) from EMP    and E.deptno=d.deptno and    E.mgr=m.empno (+) and    e.sal between S.losal and S.hisal;

10. List the names of all employees and departments who work in the same way as SCOTT and the number of departments.

1. Determine the required data sheet:

• EMP Table: Find Scott's job;
• EMP Table: employee's information;
• Dept Table: Department name;
• EMP Table: Number of departments;

2, determine the known correlation field: Emp.deptno=dept.deptno;

First step: find Scott's job

SELECT job from emp WHERE ename= ' SCOTT ';

Second Step: The above subquery returns data in a single row, so it can appear in the WHERE clause to find employee information that satisfies the requirement

Select E.empno,e.ename,e.jobfrom emp ewhere e.job= (    Select Job from    emp    WHERE ename= ' SCOTT ')    and E.ename<> ' SCOTT ';

Step Three: find the Department name

Select E.empno,e.ename,e.job,d.dnamefrom emp e,dept dwhere e.job= (    Select Job from    emp    WHERE ename= ' SCOTT ') and    e.ename<> ' SCOTT ' and    E.deptno=d.deptno;

Fourth Step: find the number of departments, need statistics, so write in the FROM clause

Select E.empno,e.ename,e.job,d.dname,temp.countfrom emp e,dept D, (    select Deptno dno,count (empno) COUNT    from EMP    GROUP by Deptno) tempwhere e.job= (    SELECT job from    EMP    WHERE ename= ' SCOTT ') and    e.ename< > ' SCOTT ' and    E.deptno=d.deptno and    Temp.dno=e.deptno;

11. List the number of employees in each wage level and the average wage of the company.

1. Determine the required data sheet:

• EMP table: Statistical data;
• Salgrade table: The salary level is obtained;

2. Determine the known associated field: Emp.sal between Salgrade.losal and S.hisal;

This program is actually a multi-field grouping, the only difference is that the grouping condition is set to the field in the Salgrade table;

SELECT S.grade,s.losal,s.hisal,count (E.empno), AVG (e.sal) from EMP e,salgrade swhere e.sal between S.losal and S.hisalgroup by S.grade,s.losal,s.hisal;

12. The name and salary of the employees and the department names of all employees whose salaries are higher than those employed in the Department 30.

1. Determine the required data sheet:

• EMP Table: Find out the wages of all employees working in 30 departments;
• EMP Table: The name of the employee that is finally displayed;
• Dept Table: Find the department name;

2, determine the known correlation field: Emp.deptno=dept.deptno;

The first step: find the wages of employees who work in 30 departments

SELECT sal from EMP WHERE deptno=30;

Step Two: above 30 doors, use the all operator to introduce an EMP table, query name and salary

Select E.empno,e.enamefrom emp ewhere sal>all (    select sal from emp WHERE deptno=30) and    E.deptno=d.deptno;

13. List the number of employees working in each department, the average salary and service duration.

1. Determine the required data sheet:

• Dept Table: Find department information;
• EMP Table: Statistics of the number, average salary, the mean length of service;

2, determine the known correlation field: Emp.deptno=dept.deptno;

The Dept and EMP tables are directly associated, using multiple-field groupings, but a calculation process is required for service life.

SELECT D.deptno,d.dname,d.loc,count (e.empno), avg (e.sal), Avg (Months_bwtween (sysdate,e.hiredate)/12) YearFROM emp E, Dept Dwhere E.deptno (+) =d.deptnogroup by D.deptno,d.dname,d.loc;

14. List all employees ' names, department names and wages.

1. Determine the required data sheet:

• EMP Table: Find employee name;
• Dept Table: Department name;

2, determine the known correlation field: Emp.deptno=dept.deptno;

SELECT e.ename,d.dname,e.salfrom emp e,dept dwhere E.deptno=d.deptno;

15. List details of all departments and number of departments.

1. Determine the required data sheet:

• EMP Table: Statistical information;
• Dept Table: Query Department information;

2, determine the known correlation field: Emp.deptno=dept.deptno;

SELECT D.deptno,d.dname,d.loc,count (e.empno) from emp e,dept dwhere e.deptno (+) =d.deptnogroup by D.deptno,d.dname, D.loc;

16. List the minimum wage for each job and the name of the employee engaged in the work.

1. Determine the required data sheet:

• EMP Table: Statistics The minimum wage for each job;
• EMP Table: Find out the employee's name;

The first step: to count the minimum wage for each position according to the position

SELECT Job,min (SAL) from the empgroup by job;

Step Two: Associate the above query with the EMP table

Select E.ename,e.job,e.salfrom emp E, (    select Job,min (SAL) from the EMP    GROUP by Job) Tempwhere E.job=temp.job and E.S Al=temp.min;

17. List the manager's minimum salary, name, department name and number of departments in each department.

1. Determine the required data sheet:

• EMP Table: Find the manager's salary, name;
• Dept Table: Department name;
• EMP Table: Number of statistics departments;

2, determine the known correlation field: Emp.deptno=dept.deptno;

First step: find the manager of all departments

SELECT Deptno,min (sal) from Empwhere job= ' MANAGER ' GROUP by Deptno;

The second step: find the name, but the above sub-query, no longer appear other fields

Select E.ename,e.salfrom emp E, (    select Deptno dno,min (sal) sal from    emp    WHERE job= ' MANAGER '    GROUP by DEPTNO) Tempwhere E.deptno=temp.dno and E.sal=temp.sal and e.job= ' MANAGER ';

Step three: join the department's name information

Select E.ename,e.sal,d.dnamefrom emp E, (    select Deptno dno,min (sal) sal from    emp    WHERE job= ' MANAGER '    GROUP by Deptno) temp,dept Dwhere E.deptno=temp.dno and E.sal=temp.sal and e.job= ' MANAGER ' and    E.deptno=d.deptno;

Fourth step: statistics of the number of departments

Select E.ename,e.sal,d.dname,res.countfrom emp E, (    select Deptno dno,min (sal) sal from    emp    WHERE job= ' MANAGER '    GROUP by Deptno) Temp,dept D, (    SELECT deptno dno,count (empno) COUNT from    emp    GROUP by Deptno ) Reswhere E.deptno=temp.dno and E.sal=temp.sal and e.job= ' MANAGER ' and    E.deptno=d.deptno and Res.dno=d.deptno;

18, list all employees annual salary, the department name, according to the annual salary from low to high ranking.

1. Determine the required data sheet:

• EMP Table: Statistical annual salary;
• Dept Table: Department name;

2, determine the known correlation field: Emp.deptno=dept.deptno;

SELECT e.ename,e.sal*12 income,d.dnamefrom emp e,dept dwhere e.deptno=d.deptnoorder by income;

19. Identify the supervisor of an employee and the name of the department, and ask for more than 3000 of the salary in these supervisors.

1. Determine the required data sheet:

• EMP Table: employee's information;
• EMP Table: Information of the leader;
• Dept Table: Department name;

2. Determine the known associated fields:

• Employees and departments: Emp.deptno=dept.deptno;
• Employees and leaders: Emp.mgr=memp.empno;

SELECT DISTINCT m.ename,d.dname,m.salfrom emp e,emp m,dept dwhere e.mgr=m.empno and M.deptno=d.deptno and m.sal>3000;

20, to find out the department name, with the ' S ' character of the department employees of the total wage, the number of departments.

1. Determine the required data sheet:

• EMP Table: Statistical information;
• Dept Table: Department name;

2, determine the known correlation field: Emp.deptno=dept.deptno;

SELECT d.dname,sum (e.sal), COUNT (e.empno) from emp e,dept dwhere e.deptno (+) =d.deptno and d.dname as '%s% ' GROUP by D.dnam E

21. For employees who have been in office for more than 30 years or hired in 87, the principle of salary increase: 10 departments grew 10%, 20 departments grew 20%, 30 departments grew 30%, and so on.

UPDATE emp SET    sal= (1 + deptno/100) *salwhere months_between (sysdate,hiredate)/12>30    OR to_char (HireDate, ' yyyy ') = 1987;

All of the above topics as a summary of DML operations, the results of these topics are not important, the key is to solve the problem of ideas, these can only be through the code of continuous practice.

Second, summary

1, multi-table query: When the query statement is written, it is necessary to determine the required data table, and as long as the table's associated query, there will be a Cartesian product problem, using the associated field to eliminate the problem.

In the use of multi-table query to consider the problem of left and right connection, the database outside Oracle can use the SQL:1999 syntax to control the left and right connections;

2, all statistical functions are used for statistical operations, and statistics to be carried out in groups (or used alone), grouping using the GROUP BY clause, is the existence of duplicate data on a column when the grouping operation is used, and the filter after grouping is done with a HAVING clause, All the grouping functions can be nested, but there are no other query fields in the grouping functions after nesting, including the grouping fields;

3, sub-query: Combined with limited query, multi-table query, group statistics query to complete the operation of each complex query, sub-query generally in where and from after the emergence of more;

4, the update operation of the database must be controlled by the transaction, two commands of the transaction: COMMIT, ROLLBACK, each user connected to the database using a session representation;

5, the Data table paging query display relies on rownum pseudo-column, later in the development must be 100% to use.

Oracle Complex queries and summaries