Resolved: SQL Stored Procedures OUT, User-Defined Functions - Question regarding Query
Hello!
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. :D
Thanks in advance :)
Best regards,
Maros J
2 answers ( 0 marked as helpful)
Hi Maros!
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.
Best,
Martin
# My version without using employees table
DELIMITER $$
CREATE FUNCTION f_emp_avg_salary_v2 (p_emp_no INTEGER) RETURNS DECIMAL(10,2)
BEGIN DECLARE v_avg_salary DECIMAL(10,2); SELECT
AVG(s.salary)
INTO v_avg_salary FROM
salaries s
WHERE
s.emp_no = p_emp_no;
RETURN v_avg_salary;
END$$ DELIMITER ; # For a comparison SELECT
COALESCE('course version') AS version,
employees.f_emp_avg_salary(11300) AS avg_salary_employee UNION SELECT
COALESCE('simple version'),
employees.f_emp_avg_salary_v2(11300);
DELIMITER $$
CREATE FUNCTION f_emp_avg_salary_v2 (p_emp_no INTEGER) RETURNS DECIMAL(10,2)
BEGIN DECLARE v_avg_salary DECIMAL(10,2); SELECT
AVG(s.salary)
INTO v_avg_salary FROM
salaries s
WHERE
s.emp_no = p_emp_no;
RETURN v_avg_salary;
END$$ DELIMITER ; # For a comparison SELECT
COALESCE('course version') AS version,
employees.f_emp_avg_salary(11300) AS avg_salary_employee UNION SELECT
COALESCE('simple version'),
employees.f_emp_avg_salary_v2(11300);
Submit an answer
