The Department name, number of employees of the Department, average salary of the department, name of the employee with the lowest income of the Department, and name of the employee with the highest income are queried.

The Department name, number of employees of the Department, average salary of the department, name of the employee with the lowest income of the Department, and name of the employee with the highest income are queried.

Example:

The Department name, number of employees of the Department, average salary of the department, name of the employee with the lowest income of the Department, and name of the employee with the highest income are queried.

This is a thinking question between emp and dept tables under Scott's default oracle user.

Employee table (emp)

Basic information of each employee is recorded

 

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 lead (manager) No.

5 hierdate date

6 sal Number (7, 2) monthly salary/salary

7 comm Number (7, 2) bonus

8 deptno Number (2) ID of the employee's Department

Department table (emp)

The basic information of each department is recorded.

 

NO field type description

1 deptno Number (2) department ID (unique)

2 dname Varchar2 (14) department name

3 loc Varchar2 (13) Address

Analysis: to query the number of employees in a department, average salary, minimum income employee name, and highest income employee name, you must first know the highest income and lowest income of the Department.

Step 1: query the Department name, Department employee count, Department average salary, Department lowest income and highest income

select deptno, count(*), avg(sal), min(sal), max(sal) from emp group by deptno;

Step 2: query the name of the minimum income.

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);

Likewise, you can query the names of the highest-income users.

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);

So how can we query the names of the minimum and highest income groups at the same time?

Step 3: Associate the highest income and lowest income of the Department with two emp tables for the first query to obtain the names of the highest income and the highest income respectively.

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.deptno = t.deptno and e.sal = t.min_sal) and (s.deptno = t.deptno and s.sal = t.max_sal);