15 Nov 2023

Posted on:

23 Apr 2023

0

# Resolved:Why do we need a join here.

Why do we need a join in this problem shouldn't the employees table suffice with the following query

SELECT year(hire_date), gender, count(emp_no)
From t_employees
group by 1,2
order by 1;

Instructor
Posted on:

24 Apr 2023

0

Hi Barun!
Thanks for reaching out.

We have to use the t_dept_emp table because this table contains the column from_date. In your query you used the column hire_date. But both columns are different.

The hire_date column indicates the date that the employee has started working. The from_date and to_date columns indicate the start date and the end date for the current work that the employee has worked. Let's check for emp_no=10001. If we run the following query:

`SELECT `
`    *`
`FROM`
`    employees`
`WHERE`
`    emp_no = 10001;`
we see Georgi Facello has started working at 1986-06-26. This is his career start for the company. But in this company he has worked at different positions during different periods. If we run the following query:

`SELECT `
`    *`
`FROM`
`    salaries`
`WHERE`
`    emp_no = 10001;`
we see all different periods of work with all salaries and the columns from_date and to_date for all his previous jobs. If he is currently working at particular position the date is set to 9999-01-01.

So, this is why we use both tables.

Hope this helps.
Best,
Tsvetelin

Posted on:

15 Nov 2023

0

SELECT YEAR(hire_date), gender, count(gender) FROM t_employees
group by hire_date, gender
order by hire_date;