MYSQL: Your company has fallen on hard times, and you have to let some of your employees go. You figure it will be easier to fire an entire department all at once so now you want to determine which department itâs going to be.
Information about your employees and department is stored in two tables, Employee and Department, respectively. Here are their structures:
Department
Id: unique department id
Name: department name
Employee
Id: unique employee id
Full_name: employeeâs full name
Department: foreign key referencing department id
Salary: employeeâs salary
To choose the unfortunate department, you set a number of criteria you are willing to get rid of any department that has no more than 5 employees. Among these smaller departments you will consider those where the total salary of all of its employees is maximal. Lastly to make a tough situation more fair, you decide to make the final choice from the remaining departments at random. Thus, youâd like to build the following table of departments:
Select all departments with less than 6 employees
Sort these departments by the total salary of its worker in descending order (in the case of a tie, the department with the greatest number of employees should go first; if itâs still not enough to break a tie, the department with the smallest id should go first)
Cross out the departments at the even row and leave only those in the odd positions to consider them more thoroughly afterwards
Given tables employees and departments, your task is to write a select statement described above. The output should have columns dep_name (the name of the department), emp_number (the number of employees in this department), and total_salary (the sum of all employees' salaries in this department) and be sorted according to the specifications above.