 Hey guys, welcome to SSULetech. So till this side and this is continuation of SQL Server interview questions and answers. So recently one of my friends has attended interview in the STL. So these questions were asked over there. So let's get started with the questions. So basically here we are having four tricky SQL queries. We need to tell the output of those queries. So let's get started. So this is the first query. So we need to tell the output of this query. So here as you can see we are declaring this at the rate i variable and initial value that is 1 and inside the CTE we are going to first displaying the value of this i and after that we are going to use the union all and here we are going to select the id plus 2. So basically this is the recursive CTE and this CTE will be going to execute until this id value is less or equals to 99 and after that we are going to select the CTE and what will be the output of this query. So first of all as you can see the initial value of at the rate i. So it is 1. So select that should be 1 and after that we are going to add 2 on this 1 because this is the id and here we are going to add on this id. So it will be going to have 3 in the next row. After that it will be going to add 2 so the next will be 5 then 7 and it will be going to execute 99 the id and it should be add 2 so that will be 101. So it is going to start from 1 and the last value that should be 101. So total value that we will be getting 51. So let me execute and we will see the output. So here we can see we are having 51 rows and the first row is 1357911 up to 101. So here you can see 101. So this output we need to tell the interviewer. So let's move to the next question. So this is the second question and we need to tell the output to the interviewer. So here you can see we are having at the rate i and at the rate j variables and initial value of the at the rate i and at the rate j is 1. After that we are going to print i and j. After that we are having this loop. So this loop will be going to execute until this at the rate i value is less than 10 and inside this loop we can see we are going to set this at the rate i value at the rate j plus 1. So first once we will execute this it will be going to print this at the rate i and at the rate j. So it will be 11 and after that we are having this loop. So in this loop we are going to set this at the rate i value that is at the rate j plus 1. So 1 plus 1 so at the rate i that will be 2 and here at the rate j value is equals to at the rate i so it will be again 2. So at the rate i and j will be 2. So here we are going to print at the rate i and j. So first we will see 11 next will be 22. After that it will be going to check this loop again at the rate i value is 2. So this loop is going to execute again and this time we are going to set this at the rate j so it is having 2 plus 1. So at the rate i will be 3 and at the rate j that will be 3 again. So it will be going to print 3 3. So 1 1 then 2 2 then 3 3 so we can execute and we can check the output. So here we can see it is executing up to 10 10. So 1 1 2 2 3 3 4 4 up to 10 10. So we need to tell this output to the interval. Let's move to next question. So this is the next question and this is very tricky question. As you can see we are having this source table and we are having this target table. Let me execute and let's see what values we are having. So here we are having this id and name. So id 11 2 3 and in the target table we are having id 1 and 2. Here we are going to use this merge statement. So inside this merge statement we are going to check the values on the basis of id and if the value is matched then we just want to update the target name with the source name. So what will be the output of this query? So this query will be failed because your source is having duplicate values in the id field. As you can see id 1 are coming two times. So that's why it will be not going to update because it gets confused. Either it will be going to update a or v in the target table. That's why this merge statement will not be going to work. So you need to remember if your source is having duplicate data then your merge statement will not be going to execute. Let me execute and you will see the error. So here you can see we are having this error. As you can see this happens when a target row matches more than one source row. So this is the condition with the id 1 and 1. So this will not going to proceed. Let's move to last question and this is again very tricky question. So as you can see we are having this scltable and here we are having id name then what mark we gain and what is the total mark. So here we got the 80 mark out of 100 then 90 out of 100 then 100 out of 100. So here in the total we are having 100 only. It may be you are having less than 100 values on this total marks. Like it is also possible the total marks will be 50 and marks gain that will be around 40 or 45 or 50. So that situation is also over here. So what is our requirement is here we just want to sort this table on the basis of percentage marks. So how we can do that? So we need to remember like when we are going to do the order by so that should be on the basis of percentage marks. Like here you can see 80 percent then 90 percent then 100 percent. So 100 percent should come first then 90 percent should come second 80 percent that should come third only for first three rows and we can check for further as well. So how we can do that? So for that we have to use the order by clause. So we can have the order by after that we can have this marks gain after that we can divide then total marks. Now if we will execute this then the output that will not be going to correct. Let me have the decreasing order as well over here. Let me execute this. So this is the invalid column name that should be like this. So now you can see we are having 100 then 95 then 85 then 88 then 87. So it is working fine up to five rows only but once we move to the very next row that will be the sixth one. So it is 89. So 89 should come on the third row. So here it is not working as we were expecting. So why it is not working? Because if we will check the value of this divide so that will be one only as you can see. Let me execute it again. So here you can see one after that it is showing 00000. Why? Because this marks gain and total marks both are integer values. That's why it is not going to return the correct output as per our expectation. So how we can do that? So for that we need to convert this marks gain or total marks as numeric. So we can make any of these two should be numeric. Now let me try to execute it. So now you can see 195, 90, 89, 88. So it is working fine as we were expecting. And if you want to check then you can also copy this and see in the select statement. Let me execute this. Now here you can see we are having the values as we were expecting. Now we can multiply with the 100 so we will get the percentage but that is not required over here. So we can remove this and we can execute this query so we can check the output as we were expecting. So these four tricky questions was asked in SCL. So if you like this video so please subscribe our channel and don't forget to press the bell icon to get the notification of our newly uploaded videos. If you need the script I will try to put this script on the description of this video or the comment section. So you can check it through in the next video.