I would like to ask you if I can leave the table ’employees’ when creating an emp_avg_salary procedure or f_emp_avg_salary function. We could use WHERE p_emp_no = salaries.emp_no without using employees table at all since we don’t need first_name or last_name.
The query becomes more simple and less there will be less typing. 😀
Thanks in advance 🙂
Thanks for reaching out.
Since the data in our database has been organised perfectly well, your intuition is perfectly correct.
We’ve written the query this way because it is supposed to work even if the salaries table contains employee numbers that have not been present in the employees table. At the same time, we consider employees as the correct table of reference for employee numbers.
Therefore, please feel free to share your query with the Community since in our case, your query is expected to deliver identical results.
Hope this helps.
Thanks for the explanation :), Maros
# My version without using employees table
CREATE FUNCTION f_emp_avg_salary_v2 (p_emp_no INTEGER) RETURNS DECIMAL(10,2)
DECLARE v_avg_salary DECIMAL(10,2);
INTO v_avg_salary FROM
s.emp_no = p_emp_no;
# For a comparison
COALESCE(‘course version’) AS version,
employees.f_emp_avg_salary(11300) AS avg_salary_employee