 Hello, Welcome to SSUnitex Social Decide and today we are going to see the top 10 SQL interview question and answers. So this is very important video. Here we will be seeing most of the interview questions that is asking in every interview. So the first question how we can find the duplicate from a table. Next we will see how we can delete the duplicate in a table. Next we will see what is the difference between union and union all. Next we will see what is the difference between rank, dense rank and row number. Then we will see how to find the second highest salary in the employee table. Next we will see how we can find the nth highest salary. Next how to find out the employee whose salary is more than their manager. Next how to swap the gender in a employee table. Next we will see count of the rows for the inner join, left outer join, right outer join, fill outer join and cross join. Next we will see how to find the department wise second highest salary. So these 10 questions if you are attending SQL server interview then these questions are very common. Most of the interviewers are asking this question. So let me quickly start with the first question how to find the duplicate in a table. So for finding the duplicate I am going to create this information table and then we are inserting the data in this table. So let me try to execute it. You can see eight rows has been inserted in this table. So finding the duplicate first we are using the row number. Inside the row number we are doing partition on the employee name then the section then contact information then the location. So all these columns like the employee name section contact info then location. So we are doing the partition on these then order by on all these four columns because the first column the serial number is the identity column. So it is going to automatic generating in this table. So for finding the duplicate here we are generating the row number and this row number will be having values like one. If we are having a duplicate then we will get the two for the similar kind of data. Next if the partition will be reset then it will again start from one. So if you can execute this query we will see it will be returning total two records those are duplicate as we can see and so this is the way by which we can find out the duplicate next how we can remove the duplicate here as we have find out the duplicate. So for deleting the duplicate we can simply delete it from the city. So how we can do that we can simply execute this second query. So it will be delete everything those row ID is more than one. So let me try to execute this query as we can see two rows affected. So two rows has been gone. So it should be having six rows. So as we can see total six rows are here. So duplicate records has been removed. Next we will see how we can check about the union and union all and what is the difference between these two. So for that I am creating again this information table. So this is the same set of data that we are having in the information table. So we can execute eight rows are here. Now if we are going to use the union then it will return the unique records between these two tables. So if we can execute then we can see total eight records because eight records are the unique records. So that's why we are seeing eight records here. If we are using the union all so it will return all the records from the table one and table two. So here we will see total 14 records because we have already removed the duplicate. So six records in the first table and eight record in the second table. Next we will see what is the difference between rank, dense rank and row number. So let me try to use the information one table and let me try to execute this query and we will see the difference between these. So here we can see this is for the row number. Next we can see for the dense rank and last we can see for the rank. So the difference is as we could see here. So we are doing the ordering on this employee name. So employee name if we are having any duplicate like we have the duplicate values here. So in this scenario row number will be going to generate the sequence order like the five then six then seven like that it will be returning. But in case of the dense rank it will return the same ranking for the duplicate values. So as we can see here we have this duplicate value. So it is returning five five for both these records. And in case of the rank it is also doing the same thing. It is also returning the same record five five. Next we can see this one is also duplicate value. So in case of the row number it will go in a sequence order. But in case of the rank previous sequence rank was five. So next dense rank value will be six. But in case of the rank it will escape the next value if the values are repeating. So here five has repeated two times. So that's why six has been removed from here and we'll see the next value as seven. So this is the difference between these three. So we have done till four. Next how to find the second highest salary in a employee table. So I have already recorded a video how we can find the second highest salary nth highest salary. So you can watch all these videos. I'll provide the link of these videos in the description of this video. So you can simply watch these videos. Next we can see how to find the employees those salary is more than their manager. So this is also very good question. So let me quickly go in SSMS. And here first let me try to create this table which is the employee table. And next let me try to insert some data in this table. So four rows has been inserted. Now here let me try to select this table first as we can see. So here the manager of RAM we can see SUMIT. So SUMIT salary is 200550 and for the RAM salary which is 200750. So which is more than SUMIT salary. So we need to write a query for fetching the RAM. So here we can use the self join and on the self join we are treating this W as a employee ID. And this M as a manager. And here we are checking the employee tables manager ID should be equals to managers table employee ID. And the salary of the employee is more than the salary of the manager. So we can simply write this query and we can execute and we'll see RAM is returning here. So this is the way by which we can simply find out the employees those salaries more than their manager. Next we can see the count of the rows for the inner join, left outer join, right outer join, full outer join and cross join. I have already recorded one video here. I'll provide the link of this video in the description of this video. So you can watch this video for getting the more in-depth knowledge for the number of rows for all type of joins. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. See you in the next video.