 Hello, welcome to SSUnitech. So, we will decide and today we are going to see top four SQL tricky interview questions and answers. So, here is the first question. So, what is the output of the below query? You can see the query which is the selected count of C1 and C1 is getting under this query. So, in this query we are having first x then we are doing union all then we are having y then we are doing union all then we are having null. So, in this inner query we will be returning x and after that we will be having y then we will be having null. So, like these three values we will be having from this inner query and after that you can see we are going to do the count of the C1. So, this is basically the C1 column. So, if we are going to do the count of these three then ideally it should be going to return as three but it will not return three. Why it is not returning? Because whenever you are going to use the aggregate function and if any null value will come under that then that null value will be ignored. Then what will be the output of this? That will be two. So, that is why interviewer has asked this question. Let me quickly go inside the SSMS and show you the output. So, here you can see we are having the same query. So, first let me try to execute this inner query. So, it should be going to return x y and null that we can see. So, if we are going to execute with the count then it's written two. Let me quickly jump to the next question. So, here is the second question that what is the output of the below query. So, in this case we can see we are having this first value and then we are doing union with all these because here we have the union all with these three. So, in these three it will be going to return x then it will return null and after that again it will return null. So, this will be for these three and then we can see A. So, A should be going to have over here. So, we are going to use union. So, that is why interviewer has asked the question whether this null will be going to return two times or not. So, null will be going to return two times because it will start execution at the first position from the top. So, first it will be executing like this union. So, it will return A x and after that it will be going to do the union all with the null. So, it will be going to have the null and next it will again have the null with the union all. So, again it will be going to have the null. So, it will be going to return like all these four rows here. So, this is the catch because we are using the union over here and interviewer wants to know whether it will be going to return three rows A x null or it will return four rows A x null two times. So, as I told you it will be going to return two times it will start executing from the top. Let me quickly jump to the SSMS and here let me quickly go into the second question and here let me try to execute. So, as we can see it is going to return A x null two times. Now jump to the next question. So, suppose there are one table those are having four columns like employee ID, employee name, department and designation. So, those are also having duplicate records on that table. So, we need to write a query and by writing that query we should be going to remove the duplicate and here without using any ranking function. So, we cannot use the row number or the rank function. So, without writing those ranking functions we have to write a query to remove the duplicate from the table. Let me quickly go inside the SSMS and we'll try to write the query. So, here let me try to create the table first. So, table is created successfully. Now let me try to insert the data into the table. So, as we can see data should be going to insert. So, as we can see data is inserted successfully. Now let me try to select and we'll see and verify the data. So, here we can see we are having total five rows, three rows for Suseel and two rows for Prabhu. But we can see these are the duplicate rows. So, ideally in the output we should be going to have only two rows one for the Suseel and second for the Prabhu. So, how we can get it? So, for that you can understand first let me try to use the group by. So, under the group by we can go with the employee name. Then we can go with the department. Then we can go with the designation. So, like these three columns are making it duplicate and similarly here inside the select we can go and try to use these three and next we can use the count. So, it will be going to return how many times it is duplicate. So, this is duplicating two times and this is duplicate three times. So, here if we can go and instead of using any aggregate function as count we can use the max or min of the employee ID. So, what it will be returning? It will be returning only a single record that is 5 and 3 because here as we can see 5 is the maximum for Prabhu and 3 is maximum for the Suseel. So, we don't want to remove these three rows. We want to remove except these two rows from here. So, simply instead of going anywhere we can directly use the delete from this table and then we can write where we can see employee ID. So, this employee ID not in we can remove everything from here and we can simply call this as EMP ID. So, what it will be returning? It will be returning two values 1 for 5 and second for 3. So, we don't want to remove these two records except these two we want to remove everything from the table. So, let me try to delete. So, as we can see three rows affected let me try to verify. So, data should be going to delete in the table and we can verify it by selecting. So, we can see we are having only non-lubricate records in the table. So, by using this approach you can achieve it. Let me jump to the last question. So, here we can see there are two tables as per below. First is the envelope and second is the document table. So, these two tables and we are inserting some records into the table. So, as envelope is having 1 1 2 2 and 3 3 and here document is having 1 5 2 6 and null 0. So, what will be the output of the below query? So, here we can see the output of the doc. So, let me try to create this table so that will be very easy to understand. So, jump to the last question here. Let me try to create the table and inserting the data into the table. So, data is inserted. Now, let me quickly go into the envelope and try to select and after that let me try to select the docs as well and try to execute this. So, here we can see it is having ID 1 2 3 and here ID number is 1 2 then we can see null. So, let me quickly go in the query and try to understand what that query is doing. So, we can go here. So, here is the query. So, what this query is doing? It is updating the doc table. So, it means the second table it is updating. Now, what it is updating? It is updating the doc text. So, this column it is updating. Now, so the condition is it is joining with the ID of the doc with the envelope. So, envelope is having ID 1 and 2. Here we have the ID 1 and 2 that we can verify. So, like these two records should be going to update but we are also having this exist condition. So, this exist condition is saying if your document value is equals to envelope ID. So, it means if this ID is a label inside the envelope table only then we want to update it. So, like this is the same kind of thing it is writing again. So, what it will be updating? It will be updating your pages sequence which we can see 5 and 6 only for the ID 1 and ID 2. For the null it will not be update anything. So, this is the output of this question. Let me try to execute this update. It was affected and if you can see then we can verify the ID 1 and 2 has been updated but null is not updated. 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.