Average Salary by Department
Mediumsql
Write a query that returns, for each department, the department name and the average salary of its employees as avg_salary, using integer division (SUM(salary) DIV COUNT(*), which drops any remainder — do not use AVG(), whose fractional result won't match). Order the results by avg_salary descending.
sqlCREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, department TEXT NOT NULL, salary INTEGER NOT NULL, manager_id INTEGER );
Sample data (this is what your query runs against when you press Run):
sqlINSERT INTO employees (id, name, department, salary, manager_id) VALUES (1, 'Asha', 'Engineering', 85000, NULL), (2, 'Rohan', 'Engineering', 72000, 1), (3, 'Neha', 'Sales', 68000, NULL), (4, 'Vikram', 'Sales', 61000, 3), (5, 'Priya', 'Engineering', 79000, 1), (6, 'Karan', 'Marketing', 55000, NULL), (7, 'Divya', 'Marketing', 58000, 6), (8, 'Aman', 'Sales', 64000, 3);
Example 1
Input
(none)
Output
department avg_salary Engineering 78666 Sales 64333 Marketing 56500
GROUP BY department collapses rows per department; SUM(salary) DIV COUNT(*) computes the truncated integer average (MySQL's / returns a decimal, DIV truncates). Reference: SELECT department, SUM(salary) DIV COUNT(*) AS avg_salary FROM employees GROUP BY department ORDER BY avg_salary DESC;