SELECT e.emp_no as employee_ID,
min(de.dept_no) as dept_code,
(SELECT emp_no from dept_manager WHERE emp_no = 110022) as manager_ID
FROM employees e
JOIN dept_emp de on e.emp_no = de.emp_no
WHERE e.emp_no <= 10020
GROUP BY e.emp_no
ORDER BY e.emp_no;
I have 2 questions regarding the code above.
- Can you please reexplain the purporse of min() function above because I still don’t get why chosing the minimum value of the department that one employee belongs to would be accurate?
- In the lecture, you said that we should join the dept_manager table to retrieve the manager_ID column. However, in the code, you chose to join the dept_emp table. Can you please help me understand why you chose to join this table instead?
Hello Vinh!
I am also a student here but I will try to give you my view on it.
As for 2. dept_manager was indeed used in retrieving the manager_ID column, see the part in your submitted code.
(SELECT emp_no from dept_manager WHERE emp_no = 110022) as manager_ID
dept_emp table was then used to retrieve dept_no for employees. There would be a possibility to use just dept_emp table instead of employees tables to get emp_no, but JOINing employees table with dept_emp will ensure, that employees are still working for the company. In dept_emp alone there are employees who no longer work for the company.
As for 1., try
SELECT e.emp_no, de.dept_no FROM t_dept_emp de JOIN t_employees e ON e.emp_no = de.emp_no WHERE e.emp_no = 10219 ORDER BY e.emp_no;
You will see that employee 10219 could work in 2 different departments. Now when you use GROUP BY emp_no you know you will get just one record. But which department will it be? MIN(dept_no) ensure that you know which was it. The one with the lowest number.
This is how I understood the query. Hope it helps. If you see some holes in the explanation, let me know. I am also learning 🙂
Best,
Maros J
Hi Maros, thanks for your response to my question. I still need your help to clarify things a bit more. As for 1, are you saying that min(dept_no) will choose the lowest value of the 2 department numbers? If so, how do we know that this is the current department number that the employee is in? For 2, why can’t u just retrieve the emp_no from the employees table directly but choose to join the employees table and dept_emp table? Thanks!
Hello Vinh, thanks for your reply. For question 1: If so, how do we know that this is the current department number that the employee is in? – For now, you just know it is the lowest number of departments. You are right, you don’t know if it is the current department, which is the employee assigned to. You could think about how you could modify the query to include this information. I think it should be possible to improve this query. However, I think that if you get the concept and you are aware of these subtle details, you should proceed to the next lectures. There is still a long trip ahead of you. For 2. if you would use the employees table only, you would not get information on which department employee is assigned to. Best, Maros J
You are absolutely right, Maros. Thanks again for the response. Now that I reexamine the code, I realized that we also selected the dept_no in our query.
Hello Vinh. Glad to help. Good luck! 🙂