Problem 2- Solution in SQL

Question

Hello everyone! In the solution of the second problem, when we select calendar_year, why we use a subquery in the from statement instead of using year(hire_date) in the select statement. I tried it and didn't work. Best,
Mohammed B,

Answer 1

Hi Bouchoucha! Thanks for reaching out. Can you please support your question with the exact query you've executed? This will help us assist you better. Looking forward to your answer.
Best,
Martin

Answer 2

This is the query i've executed SELECT d.dept_name,
e.gender,
dm.emp_no,
dm.from_date,
dm.to_date,
YEAR(e.hire_date) AS calendar_year,

CASE
WHEN YEAR(dm.to_date) >= calendar_year AND YEAR(dm.from_date) <= calendar_year THEN 1
ELSE 0
END AS active

FROM
t_dept_manager dm
JOIN
t_departments d ON dm.dept_no = d.dept_no
JOIN
t_employees e ON dm.emp_no = e.emp_no

ORDER BY dm.emp_no, calendar_year;

Answer 3

Hi Bouchoucha! Thank you very much for your reply. What is important to remember is that we cannot set alias names in the field list and use them in the calculation/formula for defining another field. This must happen, say, at a different level in our query. Since we want to use the calendar_year value in the CASE statement, we need to have defined it with the FROM clause (as opposed to the SELECT statement). Hence, the following subquery is very important to be added in the FROM clause.

    (SELECT 
        YEAR(hire_date) AS calendar_year
    FROM
        t_employees
    GROUP BY calendar_year) e

Please add it and retry running your query. Hope this helps.
Best,
Martin

Problem 2- Solution in SQL

Submit an answer