SQL interview questions:

1

06%20PM
06%20PM941×641 133 KB
34%20PM
34%20PM944×647 118 KB

About the last question listed above, the queries I wrote was this:

select salary,first_name,last_name,department_name
from e employee
join d department
on e.department_od = d.department_id
where salary in (
select max(salary) from e
)
group by department_name;

where the correct answer is this :

select salary,first_name,last_name,department_name
from e employee
join d department
on e.department_od = d.department_id
where salary in (
select max(salary) from e
where d.department_id = e.department_id
group by department_name
);

so my question is how do I know when to put where d.department_id = e.department_id
group by department_name into the bracket? () why do we need two d.department_id = e.department_id?

@camperextraordinaire (also I will take a look at the question you posted later after this, the one that I did not fully got right)

2

Somehow, I did not notice this question from you until just now.

The reason the second query requires the two places where e.department_id = d.department_id, is because that select statement is a completely different query, so in order to make the group by department_name work, the that same columns reference must be made again.

I would have used the following query to solve. Notice I did not use the two similar column references.

select max(salary),first_name,last_name,department_name
from employee e
join department d
on e.department_id = d.department_id
group by department_name
1 Like