SQL question challenge (Employee Boss)

Github Link

DDL Scripts :

Employee table

 create table employee(employee_id integer,department_id integer,boss_id 

         integer,name varchar(50),salary integer);
 
 insert into employee values(1,1,2,'John Smith',20000);
 insert into employee values(2,5,4,'Ava Muffinson',10000);
 insert into employee values(3,2,5,'Cailin Ninson',30000);
 insert into employee values(4,2,5,'Mike Peterson',20000);
 insert into employee values(5,2,1,'Ian Peterson',80000);
 insert into employee values(6,3,null,'John Mills',50000);

 +----+------------+-----------+--------+---------------+
 | id | first_name | last_name | salary | department_id |
 +----+------------+-----------+--------+---------------+
 |  1 | John       | Smith     |  20000 |             1 |
 |  2 | Ava        | Muffinson |  10000 |             5 |
 |  3 | Cailin     | Ninson    |  30000 |             2 |
 |  4 | Mike       | Peterson  |  20000 |             2 |
 |  5 | Ian        | Peterson  |  80000 |             2 |
 |  6 | John       | Mills     |  50000 |             3 |
 +----+------------+-----------+--------+---------------+
 

Department Table

 create table department(department_id integer, name varchar(50));
 insert into department values(1,'rate');
 insert into department values(2,'refund');
 insert into department values(4,'UI');
 insert into department values(3,'electric');
 insert into department values(5,'innovation');
 +---------------+------------+
 | department_id | name       |
 +---------------+------------+
 |             1 | rate       |
 |             2 | refund     |
 |             3 | electric   |
 |             5 | innovation |
 +---------------+------------+

1) SELECT EMPLOYEES WHO DO NOT HAVE A BOSS

select employee_id,name  
  from employee 
  where boss_id is null;
 +-------------+------------+
 | employee_id | name       |
 +-------------+------------+
 |           6 | John Mills |
 +-------------+------------+

2) LIST EMPLOYEES WHO HAVE BIGGEST SALARY IN THEIR DEPARTMENTS
sol 1

select employee_id, department_id,max(salary) from employee group by department_id;

sol 2

select e.employee_id,e.department_id,b.max_salary 
  from employee e 
  join (select department_id,max(salary) as max_salary 
     from employee group by department_id) as b 
     on e.department_id =b.department_id 
     and e.salary=b.max_salary;

output

        +-------------+---------------+------------+
 | employee_id | department_id | max_salary |
 +-------------+---------------+------------+
 |           1 |             1 |      20000 |
 |           2 |             5 |      10000 |
 |           5 |             2 |      80000 |
 |           6 |             3 |      50000 |
 +-------------+---------------+------------+

3) LIST DEPARTMENTS THAT HAVE LESS THAN 3 PEOPLE IN IT.

select department_id 
 from employee 
 group by department_id 
 having count(department_id) < 3;
 

ouptput

        +---------------+
 | department_id |
 +---------------+
 |             1 |
 |             5 |
 |             3 |
 +---------------+
 

4) LIST ALL DEPARTMENTS ALONG WITH THE NUMBER OF PEOPLE

select department_id,count(employee_id) as emp_count  
  from employee
  group by department_id

output

        +---------------+-----------+
 | department_id | emp_count |
 +---------------+-----------+
 |             1 |         1 |
 |             5 |         1 |
 |             2 |         3 |
 |             3 |         1 |
 +---------------+-----------+

5) LIST ALL DEPARTMENTS ALONG WITH THE TOTAL SALARY

select department_id,sum(salary) as total_salary 
  from employee group by department_id ;

output

        +---------------+--------------+
 | department_id | total_salary |
 +---------------+--------------+
 |             1 |        20000 |
 |             5 |        10000 |
 |             2 |       130000 |
 |             3 |        50000 |
 +---------------+--------------+