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

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