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.

Source: Internet
Author: User
Tags dname

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





Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.