Click here to Skip to main content
15,066,651 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted

1 solution

Please, read this: Visual Representation of SQL Joins[^] and i promise you, you'll find an answer ;)

Tip: To get result by using single select statement, you need to use HAVING clause[^].

SQL
SELECT E.EmployeeName, SUM(S.employeesalary) as Salary
FROM EmployeeDetails AS E RIGHT JOIN EmployeeSalary AS S on  E.Id= S.Id
GROUP BY E.EmployeeName
HAVING SUM(S.employeesalary)>1000
--ORDER BY Field 
   
v3
Comments
anurag19289 22-Jul-14 11:39am
   
Thanks :)

GROUP BY E.id,E.EmployeeName is missing
Maciej Los 22-Jul-14 11:43am
   
You're welcome ;)
ID is not necessary. EmployeName is obligatory.
Could you accept my answer as a solution (green button)? - formally to remove question from unanswered list.
Sergey Alexandrovich Kryukov 22-Jul-14 11:43am
   
5ed. Good reference to the good article.
—SA
Maciej Los 22-Jul-14 11:44am
   
Thank you, Sergey ;)
anurag19289 22-Jul-14 11:44am
   
thank you very much..it didn't strike me :)
Maciej Los 22-Jul-14 11:45am
   
;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900