Lists the number of employees and department numbers in each department with wages higher than the department's average salary, sorted by department number (expressed in SQL statements).

Find out the number of employees and department numbers in each department with wages higher than the department's average salary, and sort by department number (expressed in SQL statement)

1. Create a table

2. Ideas:

(1) First, check the average salary for each department

1 Select deptid,avgas fromgroup by

-----> Search Results:

(2) query using the idea of federated query: SELECT * from Table1,table2 where Table1.name=table2.name

That is, the employee table and the above table query the results of the joint query, to find all wages more than the average wage record.

1 Select table1. *  from  as table1, 2 (select deptid,avgas fromgroupby as  table23where table1.deptid= and Table1.salary  >

----> Search results:

(3) The number of employees and department numbers that are higher than the average wage in each department are queried and sorted by department number

1 SelectTable1.deptid,Count(*) fromEmployee astable1,2(SelectDeptID,avg(Salary) asAvg_salary fromEmployeeGroup  byDeptID) astable23 whereTable1.deptid=Table2.deptid andTable1.salary>table2.avg_salary4 Group  byTable1.deptidOrder  byTable1.deptid;

-----> Search Results:

Lists the number of employees and department numbers in each department with wages higher than the department's average salary, sorted by department number (expressed in SQL statements).