SQL question challenge (Employee Dept)

Link to Github

These are the list of tables which includes employees, projects and department table. It also shows the relationship between these tables

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     |
  +---------------+---------+           +---------------+---------+

1) SELECT EMPLOYEES FROM
     DEPARTMENT WHERE SALARY IS GREATER THAN 40K

select concat(employees.first_name,' ',employees.last_name) as employee_name,names 
  from employees,departments 
  where employees.department_id=departments.id 
  group by employees.id,employees.salary 
  having employees.salary > 40000;

using join

select e.id,concat(e.first_name," ",e.last_name) as employee_name, d.name,e.salary 

  from employees e 
  join departments d 
  on e.department_id = d.id 
  group by salary,e.id,employee_name,e.salary,d.name 
  having salary >40000;

simple query which displays only id

select id 
  from employees 
  where salary > 40000;

output

                +----+---------------+-------------+--------+
  | id | employee_name | name        | salary |
  +----+---------------+-------------+--------+
  |  5 | Ian Peterson  | Engineering |  80000 |
  |  6 | John Mills    | Marketing   |  50000 |
  +----+---------------+-------------+--------+

2) SELECT EMPLOYEES FROM DEPARTMENT
        WHERE SALARY IS 40K

select e.id,concat(e.first_name," ",e.last_name) as employee_name, d.name,e.salary 
  from employees e 
  join departments d 
  on e.department_id = d.id 
  group by salary,e.id,employee_name,e.salary,d.name 
  having salary = 40000;
 
#using multiple group by condition
  select e.id,concat(e.first_name," ",e.last_name) as employee_name, 
                       d.name,e.salary 
   from employees e 
   join departments d 
   on e.department_id = d.id 
   group by salary,e.id,employee_name,e.salary,d.name 
   having salary >40000 and salary < 70000;

output

                       +----+---------------+-----------+--------+
   | id | employee_name | name      | salary |
   +----+---------------+-----------+--------+
   |  6 | John Mills    | Marketing |  50000 |
   +----+---------------+-----------+--------+

3) SELECT EMPLOYEE WHO HAS MAX SALARY

select id,max(salary) 
  from employees 
  group by salary,id 
  order by salary 
  desc limit 1;
 
 #without using limit
 select id, salary 
  from employees 
  where salary in 
  ( select max(salary) from employees);
 
        #using CTE
 with result as (select id,first_name,salary, DENSE_RANK() 
   over (order by salary desc) as rnk  
   from employees)  
 select * from result where rnk=1;

output

   | id | first_name | salary | rnk |
   +----+------------+--------+-----+
   |  5 | Ian        |  80000 |   1 |
   +----+------------+--------+-----+

4) SELECT THE SECOND HIGHEST SALARY

#logic using less than
  select max(salary) 
   from employees 
    where salary < 
     (select max(salary) from employees);
   +-------------+
   | max(salary) |
   +-------------+
   |       50000 |
   +-------------+
  select id,max(salary) 
   from employees 
   where salary 
    not in (select max(salary) from employees) 
     group by id 
     order by salary desc 
     limit 1;
   +----+-------------+
   | id | max(salary) |
   +----+-------------+
   |  6 |       50000 |
   +----+-------------+
   
  with result as (select id,first_name,salary, DENSE_RANK() 
      over (order by salary desc) as rnk  
      from employees)  
      select * from result where rnk=2;
   +----+------------+--------+-----+
   | id | first_name | salary | rnk |
   +----+------------+--------+-----+
   |  6 | John       |  50000 |   2 |
   +----+------------+--------+-----+
  
 # second highest salary without using order by and limit
 select id,salary 
  from employees 
  where salary in 
   (select max(salary) 
    from employees 
     where salary not in 
      (select max(salary) 
       from employees));
   +----+--------+
   | id | salary |
   +----+--------+
   |  6 |  50000 |
   +----+--------+
# Here we have multiple sub query trying to filter max salary to the outer query
# Here we also create table based on queries
 select id,salary 
  from employees 
  where salary in (select max(t.salary) as max_salary 
    from (select max(salary) as salary 
    from employees 
    where salary not in(select max(salary) 
  from employees) group by id,salary)as t);

5) SELECT TOP 3 EMPLOYEE SALARIES IN EACH DEPARTMENT

with cte as (select id,department_id,salary,DENSE_RANK() 

over (partition by department_id order by salary desc) as rnk from employees) 

select * from cte where rnk <=2;
   +----+---------------+--------+-----+
   | id | department_id | salary | rnk |
   +----+---------------+--------+-----+
   |  1 |             1 |  20000 |   1 |
   |  5 |             2 |  80000 |   1 |
   |  3 |             2 |  30000 |   2 |
   |  6 |             3 |  50000 |   1 |
   |  2 |             5 |  10000 |   1 |
   +----+---------------+--------+-----+

6) SELECT MAX SALARY FOR EACH DEPARTMENT FOR EACH EMPLOYEE

select t.department_id,e.id,e.salary 
   from employees e 
   join (select max(salary) as max_salary,department_id 
     from employees 
     group by department_id)as t 
      on e.salary=t.max_salary and 
e.department_id=t.department_id;
   +---------------+----+--------+
   | department_id | id | salary |
   +---------------+----+--------+
   |             1 |  1 |  20000 |
   |             5 |  2 |  10000 |
   |             2 |  5 |  80000 |
   |             3 |  6 |  50000 |
   +---------------+----+--------+