21 Dec 2022

Posted on:

19 Nov 2022

0

# Different from dates

1. what is the difference between from date and hire date.
2. Why do i get a different answer when i use from_date in the salaries table instead of from_date from the dept_employees table?

SELECT
dept_name, d.dept_no, AVG(salary) AS salary
FROM
t_salaries t
JOIN
t_dept_emp de ON t.emp_no = de.emp_no
JOIN
t_departments d ON d.dept_no = de.dept_no
WHERE
t.from_date BETWEEN '1999-01-01' AND '1999-12-31'
GROUP BY dept_no
ORDER BY dept_no
;

Instructor
Posted on:

21 Dec 2022

1

Hi Pius!
Thanks for reaching out.

1. 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:
2. `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 a particular position the date is set to '9999-01-01'.
3. The different answer comes from the different records that each table contains for this particular column. You can check this with the SELECT statement comparing the result sets of the queries which select this column from these tables.

Hope this helps.
Best,
Tsvetelin