Return all the employees those salary are same in same department #SQL INTREVIEW QUESTION

 ------------------------------------------SQL Interview Question --------------------------------

---Write the SQL to return all the employees those salary are same in same department-------------

------------------------------------------------------------Sample data & Code---------------------------------------------------

CREATE TABLE [emp_salary]

(

    [emp_id] INTEGER  NOT NULL,

    [name] NVARCHAR(20)  NOT NULL,

    [salary] NVARCHAR(30),

    [dept_id] INTEGER

);

INSERT INTO emp_salary

(emp_id, name, salary, dept_id)

VALUES(101, 'sohan', '3000', '11'),

(102, 'rohan', '4000', '12'),

(103, 'mohan', '5000', '13'),

(104, 'cat', '3000', '11'),

(105, 'suresh', '4000', '12'),

(109, 'mahesh', '7000', '12'),

(108, 'kamal', '8000', '11');

-------------------------------Table got created----------------------------------------------------------------------------------------------

SELECT emp_salary.name,  emp_salary.dept_id,emp_salary.emp_id,emp_salary.salary from (
select  (ES.dept_id),ES.salary FROM   emp_salary ES
INNER Join 
emp_salary ES_1 ON ES.dept_id=ES_1.dept_id AND ES.salary=ES_1.salary
group by ES.dept_id,ES.salary
Having count (1) > 1 
) Same_Sal_Dept
inner join emp_salary on emp_salary.dept_id=Same_Sal_Dept.dept_id AND
emp_salary.salary=Same_Sal_Dept.salary
ORDER BY emp_salary.dept_id DESC