SQL/Second_High_Salary_In_Each_Dept.sql at master · SimonFans/SQL · GitHub

31 lines (27 loc) · 1.27 KB

CoderPad provides a basic SQL sandbox with the following schema.

You can also use commands like `show tables` and `desc employees`

employees projects

+---------------+---------+ +---------------+---------+

| id | int |<----+ +->| id | int |

| department_id | int |--+ | | | budget | int |

+---------------+---------+ | | | +---------------+---------+

| | |

departments | | | employees_projects

+---------------+---------+ | | | +---------------+---------+

| id | int |<-+ | +--| project_id | int |

| name | varchar | +-----| employee_id | int |

+---------------+---------+ +---------------+---------+

SELECT tb1.*

FROM employees tb1

JOIN departments tb2

ON tb1.department_id=tb2.id

WHERE (select count(distinct(tb3.salary))

from employees tb3

where tb3.salary > tb1.salary

and tb1.department_id = tb3.department_id

) =1;

Expand file tree