Problem
Average Salary Per Department
Medium- sql
Return each department with the average salary of its employees, the largest average first. Employees with no salary are left out of the average.
Expected columns: dept and avg_salary. Sort by avg_salary from high to low. Departments with the same average may appear in either order.
- Input
Employees(id, name, dept, salary) 1 | Alice | Sales | 50000 2 | Bob | Sales | 60000 3 | Carol | IT | 70000 4 | Dave | IT | NULL 5 | Eve | HR | 55000- Output
dept | avg_salary IT | 70000 Sales | 55000 HR | 55000- Explanation
IT averages 70000 because Dave's missing salary is ignored. Sales averages (50000 + 60000) / 2 = 55000, and HR's only salary is 55000. Sales and HR tie, so either may come second.
Sample database
Your query runs in the browser against these tables. Column names are exact.
Departments
- dept_id
- dept
Employees
- id
- name
- dept
- salary
Customers
- customer_id
- name
- city
- signup_date
Products
- product_id
- name
- category
- price
Orders
- order_id
- customer_id
- order_date
- status
OrderItems
- order_id
- product_id
- quantity
Logins
- customer_id
- login_date
Staff
- staff_id
- name
- title
- manager_id
No automatic grading here
Run your query and check the rows against what the question asks for. Then open the model answer to compare.
Tab indents. Press Esc, then Tab to leave the editor.
Loading the SQL engine…