Database problem--higher than the department average salary query question

Title: Identify the employee's name, salary, and department number that is higher than the average wage in your department. Average wage of the department

The structure of the table (table named Sqltest)

1, first find out the average salary of each department

SELECT AVG(salary) avg_salary ,depart_id FROM sqltest GROUP BY depart_id

Get

2. Use it as a temporary table (temp_table). Put it in the main query

SELECT sqltest.name,sqltest.salary,temp_table.depart_id,temp_table.avg_salary FROM sqltest,(SELECT AVG(salary) avg_salary ,depart_id FROM sqltest GROUP BY depart_id) temp_tableWHERE sqltest.depart_id = temp_table.depart_id and sqltest.salary > temp_table.avg_salary;

Get

Database problem--higher than the department average salary query question