at first we select the ‘d.dept_name’
but group by ‘d.detp_no’
here I do not very understand well. why we don’t group by ‘d.dept_name’ then equals to the part of select?
Hi Yanhao!
Thanks for reaching out.
I am sorry to have missed answering your question earlier. Please accept my apologies for the delayed response.
The reason is sticking to best practices. In this example, the primary key of t_departments is the dept_no column, not dept_name.
The idea is that theoretically, the same department names may have been used for different department numbers (although this is not logical, is highly unlikely, and is not what what happens in the t_employees database, it might happen by mistake and our code can be prepared for sorting the problem in such a scenario). That’s why GROUP BY the primary key column , which is dept_no.
To prove this, please feel free to refer to the DDL tab of the t_departments table.
CREATE TABLE `t_departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`),
UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Hope this helps.
Best,
Martin