I happened to notice my solution is 5x faster than the one in the text book.
After checking the execution plan, I understand that your solution did not use any indexes/keys at all but mine uses the following several indexes. Here is my solution for the reference.
SET @inquiry_date = '2000-01-01';
SET @company_average := (
SELECT
ROUND(AVG(salary_amount), 2)
FROM
salary
WHERE
@inquiry_date BETWEEN from_date AND to_date
);
SELECT
department.id,
department.name,
AVG(salary.salary_amount),
@company_average AS company_average_salary,
CASE
WHEN AVG(salary.salary_amount) > @company_average THEN 'higher'
WHEN AVG(salary.salary_amount) = @company_average THEN 'same'
WHEN AVG(salary.salary_amount) < @company_average THEN 'lower'
END AS department_vs_company
FROM
salary,
department_employee_rel,
department
WHERE
salary.employee_id = department_employee_rel.employee_id
AND department_employee_rel.department_id = department.id
AND @inquiry_date BETWEEN salary.from_date AND salary.to_date
GROUP BY department.id , department.name
;
I happened to notice my solution is 5x faster than the one in the text book.
After checking the execution plan, I understand that your solution did not use any indexes/keys at all but mine uses the following several indexes. Here is my solution for the reference.