Employee Department Interview Questions, Sql Server

Employee Department Interview Questions

/* 
Question 1: Employee Salary Department
A 1000 IT
B 2000 IT
C 3000 IT
D 4000 HR
E 2000 HR
F 1500 IT
G 7000 HR
Write a query to get results like below -
Employee Salary Next highest salary(in same department) Department
A 1000 2000 IT
B 2000 3000 IT
.
.
E 2000 4000 HR
*/

create table Employee(id int,name varchar(255),salary int,dept int);
insert into Employee values(1,'pallavi',200,1);
insert into Employee values(2,'El',300,1);
insert into Employee values(3,'Shinchan',300,2);
insert into Employee values(4,'Monika',100,2);

select * from Employee;
select 
a.name,
a.salary,
lead(a.salary) over (partition by a.dept order by a.salary) as next_highest_salary_from_same_dept
from Employee a;
/* 
Question 2: Find count of emplyees in each department.
If there are no employees in certain department then populate as 0
*/

create table departments(dept_id int,dept_name varchar(200));
insert into departments values(1,'IT');
insert into departments values(2,'HR');
insert into departments values(3,'Design');

select depts.dept_name,
case 
when count(emp.id)=NULL then 0 else count(emp.id) 
end as 'Total Employees'
from
(
    select * from Employee
) emp
right join
(
    select * from departments
)depts
on emp.dept = depts.dept_id
group by depts.dept_id,depts.dept_name
order by count(emp.id)
;


run \| edit \| history \| help	0