 Hey guys, welcome to SSUnitech, society side and this is continuation of SQL Server interview question silencers. So recently one of my friend has attended interview in Samsung. So today I am going to discuss those questions which was asked over there. So let's move to next slide to see about the questions. So here you can see we are having 10 list of questions. First what is the order of execution of select query. So interviewer has specified a query. So he wants to know about the execution of that query. So we will see later. Next question can we use the alias name inside the order by clause. Next what are the window functions available inside the SQL Server. Next what are the difference between rank and dense rank. Next how many types of joints available inside the SQL Server. And briefly explain about those. Next question how many types of recursion happens inside the CTE. Next how many types of execution plans available inside the SQL Server. Next what is RID and key lookups in SQL Server. Next how many types of indexes inside the SQL Server. And last what is the XML index. So these are the questions which was asked. So let's start one by one and see in the practical. So first what is the order of execution of select query. So move to SQL Server management studio and we will see in practical. So this is the query which interviewer has specified and wants to see the execution of this query. So here basically as you can see select distinct product ID comma sum of line total as line total. From your table where order quantity is greater than or equals to 2. Then group by with the product ID having if the sum of line total is greater than or equals to 500. Then order by of the product ID. Then what is the execution of this query. So it is going to start with the from. So first it will execute from then it is going to execute where clause. So from then where then group by then having. So these are going to execute like from where group by having. After that it is going to execute the select statement. So inside the select statement as we can see we are having this sum. So before going to select it is also going to execute the windows functions. So some is available so it is going to execute some then select after that it is going to execute the distinct. So the next that will be distinct and after that we will be going to have the order by. So this is the execution of this query. Let me repeat again first from second where third group by fourth having fifth sum with the line total in the select list. Next select next distinct and last that will be order by clause. So this is the execution of this query and the total execution of the query that will be like from with the joints then where group by having then if we have any windows functions then select distinct then union after that order by clause and at the last it is going to use the limit or offset. So this is the complete list of execution of any query. Move to the next question. So in this question can we use the alias name inside the order by clause. So here as we can see in this query we are having select distinct of product ID then sum of line total as LT which is our column name. Then from our table group by with this product ID and order by instead of this sum of line total we are going to use this alias name which is LT. Let me try to execute and we will see. So here like it is going to return the output as we were expecting. So LT is going to order by accordingly and it is going to return the output. So we can say like yes we can use the alias names inside the order by clause. Move to the next question. So next question what are the windows functions available inside the SQL server. So here basically windows functions are having three different categories. First is the aggregate next is the ranking function and last that will be values function. So these functions are come inside the windows functions category. So inside the aggregate it would be sum min max average count inside the ranking function it would be rank dense rank row number and inside the values it would be lead lag first value last value and many more. So these are the windows functions. Move to the next question. So in the fourth question it is asking like what are the difference between rank and dense rank. So here as you can see in this query we are having select order ID then rank with order by of the sales order ID. Same thing with the dense rank. Let me try to execute and we will see in the output and we will try to differentiate over there. So here as we can see order IDs are same from 1 to 12. So here we can see inside the rank it is 1 and inside the dense rank it is again 1. But while we are going to move the next order ID then we can see inside the rank it is going to skip the sequence. Like next row that will be 13 so it will be going to 13. And inside the rank it is not going to skip any numbers so previously it was 1 so next time it is 2. So this is the only difference between rank and dense rank. Now move to next question. So next question how many types of joints are available inside the SQL Server and briefly explain those. So here as we can see SQL Server has 3 types of joints. First is inner joint, next is outer joint and last is cross joint. So outer joint is also categorized into 3 types. First is left outer joint, then right outer joint, then full outer joint. So in case of inner joint it is going to return the common records between 2 tables. And in case of left outer joint it is going to return the common records as well as non-common records from the left table. In case of right outer joint it is going to return the common records as well as non-common records from the right table. Next is full outer joint it is going to return the common records as well as non-common records from the left table and right table. Then we can see the cross joint so in case of cross joint it is going to return the Cartesian product between 2 tables. Let's assume first table has 4 rows and second table also has 4 rows so the output that will be 16 rows. Because here we cannot use the on condition. Move to the next question. So in the sixth question it is asking like how many recursion can be happened inside a CTE. So that will be 100 times and we will see in the practical over here. Like in this CTE you can see we are going to use the recursive CTE without any condition. So this will be going to execute with infinity times. First query is going to return top 5 from the sales order header table. Then we are going to do the union all with the CTE. So first time it is going to return 5 rows from the first table and put in the CTE. Second time we are going to put all those inside the CTE and doing the union between those. Here we don't have any condition so it is going to do the append again and again. Let me try to execute and we will see how many times this recursion will be happened. So let me try to execute it. So here we can see the statement terminated because the maximum recursion 100 has been executed. So we can say that like 100 times this recursion can be done inside a CTE. Move to the next question. So in the seventh question like how many types of execution plans are available inside the SQL server. So here we are having two types of execution plan. First is estimated execution plan. Next is actual execution plan. Inside the estimated execution plan we are not going to execute the query. Without executing the query we are going to estimate what will be the execution plan of the query. So let me try to execute only for estimated execution plan. So we can select that query and in the right top you can see this button. You can say like display estimated execution plan. Now we can click on that and it is going to return what will be the estimated execution plan. So this is the first type. In the second type once your query will be executed. So what actual plan that query used will be going to show over there. So here you can see this button. You can enable the actual execution plan and let me try to execute the query. So your query is executed and you can see the execution plan. So you can move and you can check this is the actual execution plan of the query. So here we can say we have two types of execution plans. Move to the next question. So here like it is asking what are the key lookups and RID lookups and what are the differences between those. So for that I would strongly recommend to watch this video inside the SQL Server performance tuning part 15 where I have explained about these two in details. And the link is available over here as you can see. I will try to put this link in the description of this video. So you can directly go and check that video. Move to next question. So the next question what are the types available for the indexes inside the SQL Server. So here you can specify only the type names nothing else. So first you can say cluster index, second non cluster index, column store index, filtered index, examine indexes. So these five indexes are going to use very common. Next we have spatial index, then unique index, full text index and hash index. So this you can also specify along with these five. So this is the type of indexes inside the SQL Server. Move to the last question. So what is an XML index? So an XML index is an index type that is specifically built to handle indexing with the XML data types in a column. And here we are having two different types of XML indexes. First is the primary and second is the secondary. So we will explain in details in next videos. So here you can specify XML indexes are going to create only in the XML data type columns. And here XML indexes are mainly two types. First is the primary and second is the secondary. Next how we can create the primary XML index. So for that you can see we are having a data type. We are having a column in this person.person table. And this is the column name. So we can directly specify create then primary then XML then index. After that the index name on your table. And after that you can specify inside the bracket with the column name. So this is by which we can create the primary XML index inside a table. So these are the questions which was asked inside the Samsung. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. See you in the next video.