Problem
Employees Above Their Own Department's Average
Hard- sql
- subqueries
Return the employees who earn more than the average salary of their own department, not of the whole company. Employees with no salary are never returned.
Expected columns: all four columns of Employees (id, name, dept, salary), in that order. Row order does not matter.
- 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
id | name | dept | salary 2 | Bob | Sales | 60000- Explanation
Sales averages 55000, and Bob's 60000 is above it. In IT the only salary on record is Carol's, so the department average equals her own salary and she is not above it. Eve is alone in HR, so she is not above her own average either.
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…