The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

SQL Stored Procedures OUT, User-Defined Functions – Question regarding Query

SQL Stored Procedures OUT, User-Defined Functions – Question regarding Query

Super Learner
0
Votes
2
Answer

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. 😀
 
Thanks in advance 🙂
 
Best regards,
Maros J

#, #
2 Answers

365 Team

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

Thanks for the explanation :), Maros

6 days

Super Learner
0
Votes

# 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);