SQL QUERY TO Highest and Lowest Salary Employees in Each Department-

 ----L&T SQL Interview Problem | Print Highest and Lowest Salary Employees in Each Department---------

create table employee 

(

emp_name varchar(10),

dep_id int,

salary int

);

delete from employee;

insert into employee values 

('Siva',1,30000),('Ravi',2,40000),('Prasad',1,50000),('Sai',2,20000)

SELECT * FROM employee

---Sample Table Creation--------------------

---Solution------------------------

WITH max_min_salary AS(
SELECT *,max(salary)OVER(PARTITION BY dep_id)AS max_salary,
min(salary)OVER(PARTITION BY dep_id)AS min_salary
FROM employee)

SELECT dep_id,max(CASE WHEN salary=max_salary THEN emp_name END) AS max_salary_employee,
 max(CASE WHEN salary=min_salary THEN emp_name END) AS min_salary_employee
FROM max_min_salary
GROUP BY dep_id

------------------END OF THE SOLUTION--------------