Question Asked in interview on Join

See more:

SQL-Server

I was asked in interview a question today

create table EmployeeDetails
(
  Id int primary key not null,
  EmployeeName varchar(50)
)

create table EmployeeSalary
(
  
  Id int references EmployeeDetails(Id),
  YearofSalray varchar(10),
  EmployeeSalary int
)

alter table EmployeeSalary
add  MonthOfSalary  varchar(10)


Id	YearofSalray	EmployeeSalary	MonthOfSalary
1	2013	             3000	jan
1	2013	             4000	feb
1	2013	             4000	feb
2	2013	             3000	jan
2	2013	             3000	feb
3	2013	             3000	jan
3	2013	             3000	feb
3	2013	             6000	mar

--Output Should be Employee Name who earned more than 10000 in 2013
--From Above it should be Anurag and Siba

I answered them in two ways using cte and subquery.

select empd.EmployeeName from EmployeeDetails Empd
join
(select SUM(employeesalary) as Salary,id as Id from EmployeeSalary
 group by id
)A on  Empd.Id= A.Id
where A.Salary > 10000

2)
with cte(salary,id) as
(
select SUM(employeesalary) as Salary,id as Id from EmployeeSalary
 group by id
)
select empd.EmployeeName from cte c
join EmployeeDetails Empd on Empd.Id=c.id
where c.salary > 10000

3)CTE subquery
with cte(salary,id) as
(
select SUM(employeesalary) as Salary,id as Id from EmployeeSalary
group by id
)
select EmployeeName from EmployeeDetails where id in
(select id from cte c
where c.salary > 10000)

But they were asking me to use single select clause using join/left join. They were asking me not to use subquery and cte. Is it possible to do in single select clause ? Kindly help me if it can be done using single select clause using join/left join.