508. Above Department Average
MediumCorrelated Subquery
Return the name and salary of employees who earn more than the average salary of their own department, ordered by name.
departments
| id | name | city |
|---|---|---|
| 1 | Engineering | Chennai |
| 2 | Sales | Mumbai |
| 3 | HR | Pune |
| 4 | Research | Bengaluru |
employees
| id | name | salary | dept_id | manager_id | hired | |
|---|---|---|---|---|---|---|
| 1 | Asha | asha@corp.in | 120000 | 1 | NULL | 2019-04-01 |
| 2 | Ravi | ravi@corp.in | 90000 | 1 | 1 | 2020-06-15 |
| 3 | Meena | meena@corp.in | 85000 | 2 | 1 | 2018-01-10 |
| 4 | John | john@corp.in | 60000 | 2 | 3 | 2021-09-01 |
| 5 | Sara | sara@corp.in | 95000 | 1 | 1 | 2022-02-20 |
| 6 | Imran | imran@corp.in | 70000 | 3 | 1 | 2020-11-05 |
| 7 | Lata | lata@corp.in | 55000 | 2 | 3 | 2023-03-12 |
| 8 | Kiran | asha@corp.in | 90000 | 1 | 2 | 2021-07-30 |
Your query runs on a fresh copy of these tables (SQLite). Column names in your result do not need to match.
Loading the MyLoginn IDE…