 Hello friends. Welcome to SS Unitech. My name is Suseel Singh and this is continuation of SQL Server interview questions and answers. So in this session we will understand how we can calculate the maximum department wise salary with employee details. So let's move to another slide where we will understand what it means. So this is our another slide. Let's assume you have two input tables. First is related to employee and second is related to department. In employee table you can see you have four columns. First is ID, second is name, third is salary and fourth is department ID. And in department table you have only two columns. First is department ID and second is department name. So we want to pick the maximum department wise salary and we also want to display the information of that employee which has the maximum salary on that department. So in department ID one we can see we have two records. First is join and second is Howard. So join salary is 50,000 and Howard salary is 60,000. So we can see 60,000 is the maximum salary in IT department. So we want to display four columns in our output. First is ID, second is name, third is salary and fourth is department name. So we can see here Howard has the maximum salary. So we want to display the record of Howard with their department name. And for the HR department we can see we have two records. First is Peter and there is Rosie. So we can see Peter's salary is 70,000 and Rosie's salary is 40,000. So we want to display Peter's record because it has the maximum salary in HR department. So how we can achieve this by using SQL Server. So let's move to SQL Server management studio where we will understand how we can calculate this. So I have already typed the query so it will save the time. So this is our table. We need to write the create table and then the table name. So we have to input tables. So we have to create these two tables. First is employee and second is department. We can execute and table has already created. So first I would like to drop this table and then it will be created. So I'm going to drop it has been dropped. Now I'm going to create employee table. So employee table has been created successfully and then I want to drop department table. So we can drop once this table has been dropped. So now I want to create this table. So we can simply execute this statement to create department table. So department table has been created successfully. Now I want to insert some records on these two tables. First employee and second department. So first I want to insert record in employee table. So this query has been executed and seven rows affected. So in our slide we can see we have seven rows. So all these rows has been inserted in our table. Now move to department table. In department table we have only three records. So we can execute this query to insert all these three records in our department table. So three rows affected. So we can select and we can check whether these records has been inserted successfully or not. These two tables has been created successfully and we have successfully inserted records on these two tables. Now we need to write the query where we will understand how we can achieve our output. So first of all I would like to put inner join between these two tables. So select from employee and we are going to assign area's name is E and then I'm going to put inner join between these two tables then department table and then as D. So we can put inner join on the behalf of department ID. So department ID of A and department ID of E. So these two IDs will be same then we want to calculate the maximum department wise salary. So we want to department ID. So department ID is present all these two tables. So we can any from these two tables and then department name is present only in department table. So we want department name from department table. Then we want to calculate the maximum salary salary is present in employee table. So we can simply pick the salary then we can put some area's name maximum salary and we have to put the group by so group by D dot department ID and then D dot department name. So now I am going to execute this query once we can execute this query we can see we are going to get the result. But we want to display the employees information as well as we can see here we have calculated the department name and salary but we don't have ID and name. So how we can achieve this? So first of all I am going to put this query in a CTE. So we can use CTE by using CTE we have to specify semicolon then with then the CTE name. So I am going to use CTE temp then we have to use as then we can start a bracket and then close the bracket. And now I am going to use select a stick from this CTE. So this CTE and we can execute and we can see we are going to get our result. And now I am going to put inner join between this CTE and our employee table. So this is our employee table once we are going to put inner join between these two tables and this is our CTE table and this is our employee table. So we are going to put inner join on the behalf of department ID and maximum salary. So we can put C dot department ID is equals to E dot department ID. And then C dot maximum salary. So this is our maximum salary and E dot salary. So now here we want only four columns in our output. So first column is employee ID then employee name then the CTE dot department name. And before that we want CTE dot maximum salary. And now I am going to execute this whole query. It will return our desired output as we have displayed in our slide. So we have employee ID 3, 2 and 5. So here we can see 2, 3 and 5. So all these records has been coming as we want. So I hope you have understand how we can achieve this output by using SQL server. So if you like this video please subscribe our channel for many more videos. If you need this script it will label on my Facebook page you can copy from there. Thank you so much for watching.