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

Source: Internet
Author: User
Tags dname

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

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.