28 Nov 2022

Posted on:

21 Nov 2022

0

The solution has two select statement

Please i need an explain to the solution. I don't understand why the solution has two select statement. In the first statement, we had two variables declared. So i was looking at how two statements can be returned. Then i saw another select statement. That really confused me. Please explain , also can this question be solved with just one select statement.

The video explanation was clear and easy to understand but the assignment is hard

Instructor
Posted on:

23 Nov 2022

0

Hi Sandra!
Thanks for reaching out!

Could you please explain which code you are referring to and paste a screenshot? This can help us assist you better. Thank you.

Best,
Tsvetelin

Posted on:

24 Nov 2022

0

Instructor
Posted on:

28 Nov 2022

2

Hi Sandra!
Thanks for reaching out.

1. The first one we create to feed data into `v_max_from_date`, then we use this value in the second SELECT statement.
We can solve it with one SELECT statement also. The code is:
`DELIMITER \$\$`

`CREATE FUNCTION f_emp_info_v2(p_first_name varchar(255), p_last_name varchar(255)) RETURNS decimal(10,2)`
`DETERMINISTIC NO SQL READS SQL DATA`

`BEGIN`

`DECLARE v_salary decimal(10,2);`
`SELECT`
`    s.salary`
`INTO v_salary`
`    FROM employees e`
`        JOIN`
`    salaries s ON`
`    e.emp_no = s.emp_no`
`WHERE`
`    e.first_name = p_first_name`
`AND e.last_name = p_last_name`
`ORDER BY s.from_date DESC`
`LIMIT 1;`

`RETURN v_salary;`

`END\$\$`

`DELIMITER ;`

`SELECT f_emp_info_v2('Aruna', 'Journel');`

Hope this helps.
Best,
Tsvetelin