Last answered:

05 Jan 2021

Posted on:

04 Jan 2021

1

SQL Subqueries nested in SELECT and FROM. MIN() function and JOIN

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.

  1. 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?
  2. 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?
1 answers ( 0 marked as helpful)
Posted on:

05 Jan 2021

0

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

Submit an answer