Title:
Query the name of the department, the number of employees in the department, the average salary of the department, the name of the lowest-income employee and the highest-income employee
This is a study questions between the EMP and Dept tables under the default Scott user of Oracle.
Employee table (EMP)
Records the basic information for each employee
NO Field type description
1 empno Number (4) Employee ID
2 ename VARCHAR2 (10) Employee name
3 Job Varchar2 (9) Job title
4 Mgr Number (4) employee's lead (manager) No.
5 Hierdate Date Entry dates
6 sal Number (7,2) Salary/salary
7 Comm Number (7,2) bonus
8 Deptno Number (2) ID of the employee's department
Department Table (EMP)
Records the basic information for each department
NO Field type description
1 Deptno Number (2) Department ID (unique)
2 dname VARCHAR2 (14) Department name
3 Loc Varchar2 (13) address
Analysis: To find out the number of employees in the department, the average wage, the minimum income employee name and the highest income employee name, must first know the department's highest income and the lowest income
The first step: to find out the department name, department employees, department average salary, department minimum income and the highest income
Select Deptno, COUNT (*), AVG (Sal), Min (Sal), Max (SAL) from the EMP group by DEPTNO;
Step two: Check the name of the lowest-income person
Select E.ename from emp E, (select Deptno, COUNT (*), AVG (Sal), Min (sal) min_sal, Max (SAL) max_sal from EMP Group by Deptno ) T where E.deptno = E.deptnoand (e.sal = min_sal);
Similarly, you can also check the names of the top earners
Select E.ename from emp E, (select Deptno, COUNT (*), AVG (Sal), Min (sal) min_sal, Max (SAL) max_sal from EMP Group by Deptno ) T where E.deptno = E.deptnoand (e.sal = max_sal);
How do you check the names of the lowest earners and the top earners at the same time?
The third step: to the first query out of the department of the highest income and the lowest income simultaneously linked two EMP tables, respectively, to obtain the highest income and the highest income person's name
Select D.dname, t.nu, T.avg_sal, T.min_sal, T.max_sal, E.ename, s.ename from Dept D, EMP E, EMP S, (select Deptno, COUNT (* ) nu, avg (sal) avg_sal, Min (sal) min_sal, Max (SAL) max_sal from EMP Group by deptno) Twhere D.deptno = T.deptno and (E.DEP TNO = T.deptno and e.sal = T.min_sal) and (S.deptno = T.deptno and s.sal = t.max_sal);
Query the name of the department, the number of employees in the department, the average salary of the department, the name of the lowest-income employee and the highest-income employee