Second_High_Salary_In_Each_Dept.sql

/*
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     |
| first_name    | varchar |     |  |  | title         | varchar |
| last_name     | varchar |     |  |  | start_date    | date    |
| salary        | int     |     |  |  | end_date      | date    |
| 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;