Above Department Average
HardSQLDatabaseSorting
Description
Write a SQL query to return the name of every employee whose salary is strictly greater than the average salary of their own department. Order by name ascending. **Output columns (in order):** name
Table:
employees| Column | Type |
|---|---|
| id | INT |
| name | TEXT |
| salary | INT |
| department | TEXT |
Examples
Input:
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1,'Ann',100,'A'),(2,'Ben',300,'A'),(3,'Cal',200,'B'),(4,'Dot',100,'B');Output:
Ben
CalExplanation:
Each employee is compared against the average salary of their own department, keeping only those who exceed it.
Input:
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1,'A',10,'D'),(2,'B',20,'D'),(3,'C',30,'D');Output:
CExplanation:
Each employee is compared against the average salary of their own department, keeping only those who exceed it.
Input:
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1,'Zoe',900,'E'),(2,'Amy',100,'E'),(3,'Bob',100,'E');Output:
ZoeExplanation:
Each employee is compared against the average salary of their own department, keeping only those who exceed it.
Constraints
- •
Use standard SQL (SQLite).