I have a database with three tables that I need to return the name of the department with the highest salary costs from using SQL, what would be the most efficient query to do this. Been racking my brain with this one all day.
|----------------------------------------|
| Departments |
|---------------------|------------------|
| id | name |
|---------------------|------------------|
| 1 | physics |
|---------------------|------------------|
| 2 | math |
|---------------------|------------------|
|------------------------------------------------|
| Empoyees |
|------------|------------------|----------------|
| id | name | department-id |
|------------|------------------|----------------|
| 1 | mike | 1 |
|------------|------------------|----------------|
| 2 | frank | 1 |
|------------|------------------|----------------|
| 3 | bob | 2 |
|------------|------------------|----------------|
|----------------------------------------|
| Salaries |
|---------------------|------------------|
| employee-id | salary |
|---------------------|------------------|
| 1 | 1000 |
|---------------------|------------------|
| 2 | 2000 |
|---------------------|------------------|
| 3 | 3000 |
|---------------------|------------------|
You can join and aggregate, then order and limit:
select d.name, sum(s.salary) sum_salary
from departments d
inner join employees e on e.department_id = d.id
inner join salaries s on s.employee_id = e.id
group by d.id, d.name
order by sum_salary desc
limit 1
limit is not supported in all databases, but they (almost) all have alternatives.