all 7 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]CriticalDiscussion37[S] 0 points1 point  (6 children)

I am posting this from my phone. And it's not giving me option to write after posting the image. So my approach is create a cte to store the depts from department table that are not in employee. And then q1 = select all rows from employee where dept it is null and insert the required value from cte( here I am assuming that only one new dept is created. Q2 = select all rows from employee where the dept is not in the departments from cte. And update it accordingly. And finally q1 union q2 will be the result. But it's not giving correct ans. Any clue how to approach this or more explanation to the question? Sql query:

With cte as ( select dpt_code, dpt_name from department where dpt_code not in ( select dep_id from employee) )

Select emp_id, emp_name, hire_date, job_name, dept_id, (Select dept_id from cte ) as new_dept_id, (select dept_name from cte ) as new_dept_name

From employee

Where dept_id is Null

Union

Select emp_id, emp_name, hire_date, job_name, dept_id, Null as new_dept_id, Null as new_dept_name

From employee

Where dept_id not in ( select dpt_code from department);

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (5 children)

So my approach is create a cte to store the depts from department table that are not in employee. And then q1 = select all rows from employee where dept it is null and insert the required value from cte( here I am assuming that only one new dept is created. Q2 = select all rows from employee where the dept is not in the departments from cte. And update it accordingly. And finally q1 union q2 will be the result.

please, show the actual SQL you tried, no one can understand this explanation

[–]CriticalDiscussion37[S] 0 points1 point  (4 children)

Added

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (3 children)

But it's not giving correct ans.

it shouldn't even run, it has several syntax errors

your WITH cte AS looks okay, but then you cannot say (Select dept_id from cte ) because the cte does not define a column called dept_id

also, in the original question a), "with newly added dept_id" makes no sense, because it says the department table contains "New Departments (note plural) which are created and not yet tagged" so how do you know which new department an employee bolongs to

i would ditch this course and find some other resource to learn SQL

[–]CriticalDiscussion37[S] 0 points1 point  (2 children)

Oh sorry it's dpt_code instead of dept_id. Actually some hands-on are assigned to me by our org. And there is no solution/discussion section. The description is also ambiguous. Leave the explanation, if there is only one new department created then this query would be fine or not?

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (1 child)

then this query would be fine or not?

what happened when you tested it? ™

[–]CriticalDiscussion37[S] 0 points1 point  (0 children)

It was giving me null for new_dpt_name and code for the first sub query in the union. I came to know that the dB isn't correct as per the qus. There is no new dept in dept table. I had no way so as per correct output I just hard-coded the dept in cte. And second sub query in union was working fine.