Hello guys, my name is Susheel Singh and today I am going to tell you about the SQL Server interview questions and answers. The first question is what are the differences between table variable and temporary tables? So there is a lot of difference available in SQL Server which can differentiate between them. So very first and basic is syntax. Basically in case of table variable, table variable are not support DDL statements like you know alter create drop and you cannot change the structure of table variables also. But in case of temporary table, temporary table are support DDL statements like you know alter create drop. So you can change the structure of temp tables after it is created. So you can see in SQL Server I have typed the query so you can see at the time just here when we are going to use any table variable you need to put declare and then that variable name then type and then the column names and we want to put some dummy records and put the select. So we can see here it has been created and if you want to add an additional column is it possible or not? So in case of table variable it is not possible it will throw an error yeah we can see if you want to drop that table is it possible or not? If basically you cannot drop the table you can see if you want to delete a record from here it is possible you can do it. You can see here 4 row affected, 4 row for this union, 4 row for this select and 4 row for this delete. So in case of temporary table first you need to create the table it has been created and then put the some values on it you can add and then put the and you can select there is 3 columns and we want to one more columns. One more column we can use ultra so we need to put here and you can see and gender column has been added. If you want to delete record from this table you can put simply delete if you want to drop the table you can drop. So in case of temporary tables it is possible but in case of table variable it is not possible in case of storage table variables are stored in tempdb and it will available for batch of execution or inside the store procedure in case of tempdb it is also available in tempdb and it will available either session will closed or you can explicitly drop the table you can see I have typed the query so you can copy from here paste into new tab so there is 11 tables are available I have dropped the table so we have to create that table first there is 12 tables so we can say that temp tables are existing tempdb in case of table variable there is 12 rows so we can say there is 12 temporary tables or table variables available we are going to put the left we can see here there is 13 so we can say that table variable and temporary table both are existing tempdb next in transaction table variable cannot participate in explicit transaction but temporary table can participate in explicit transaction what it mean we can see here we declare table variable and then insert one record and then roll back that record then select so we can see that this record is exist we have used roll back but this record is not going to drop so explicit transaction are not used in table variable in case of temporary table we can say that explicit transaction is possible one row factor and you can see there is no record so transactions next in user defined function so user defined function are not allowed table variable on it sorry user defined functions can allow only table variable on it temporary tables are not allowed inside the user defined function you can see here my user defined function first I have used temporary table cannot access temporary table from or within a function so we can say that inside function you cannot use temporary table but we can use already exist so we change the name so we can say that if you are going to use table variable inside the function you can nest index indexes you cannot add indexes to a table variable explicitly and in case of temporary table you can add indexes to a temporary table both implicitly and explicitly what does been by implicit and explicit basically when we are going to create any table and there is a any column with primary then we know a unique cluster index has been created automatically so this is our implicit and if we create any table and then put the indexes on it then it is explicit so in case of table variable we cannot add explicit we can see here copy this query paste into new tab is it possible to create an index explicitly no we cannot in case of temporary table yeah we can nested scope so scope of table variable is a batch or a store procedure it will only batch or store procedure but in case of temporary table it depends what type of temporary table has been created like you know there is two types of temporary tables available in SQL Server first is local and global and there is many more differences between local and global temporary table I'll explain some here and in my next video or upcoming video I'll explain the differences between local and global temporary tables so the very first is I need to create a table with the single hash temp1 and it's id then integer so it's our local temporary table and if I put select s tick from table name you can see and in my next session sorry select s tick so it's not it's not available in another system if we are going to use double hash and for global we can see here this is available here and in case another query window it's also available it will available until this query window will not be closed or you explicitly drop this global temporary table or close the session if you close this query window and you want to select you cannot access that table that's it for today and thank you so much for watching this video if you like this video please subscribe my channel and like and thank you so much hello guys my name is Susheel Singh and today I am going to tell you a very tricky question we have a table and it has four columns first is item code second is amount 30 started and fourth is ended and we have a scenario for this for this purpose we have item code same for the all all these rows and amount may or may not be differed with with the very next rows like for between these days amount of this item is 165 and for these date amount would be 146 and for these date amount is 162 and again for these days amount is 162 again 162 so we don't want to put duplicate record in our output we would put only a single row by which start date would be 27th of December and end date would be 20th of Feb so we can see in our output so for these three rows we have only one row in our output and start date and end date would be differed according to our scenario so we have written SQL query by which we can calculate it so basically the logic behind it we will put first row item code amount start date and end in a variable and we will find out for the item code would be same and amount you know amount is differ or not for the very next row is if amount will not be differed for the next row then move to the third row if amount is differed for the second row like we have 165 and then 146 so we will go through a loop and if amount will not be same for these two row first row will be inserted as it is in our output as we can see here and for the very second row 146 and third row is 162 so amount is differ for these two rows so this row will be inserted as it is and for the third row it will check for the very next row if amount is same then move to the third row if amount is same then move to the very next row if amount will not be same then it will pick the end date of you know last 162s amounts end date and it will insert in our output so we can see here we have a table and we will generate a table with the same structure by which we will insert the desired output so this is the output table so output table has been created and we will insert input rows with row number as so we can see here row number has been generated and first we put the max row number or we can put the count of these row by which loop will be repeated again and again and we will take at the rate i1 variable and it has been assigned by one so if this loop will be repeated until and unless one is not one is a less or equal to at the rate i and at the rate i value would be eight so this loop will be repeated eight times so we have so here we have five five variables first is amount second is started third is ended and then item code and next is at the rate item one basically in the at the rate item one we will pick the amount of very next row so again we put a loop by which if amount is same for the first and second row and and this condition will be satisfied it means that you know at the rate i1 is less or equal to at the rate i so we go inside the loop and by which at the rate i1 will be increased by one and we will put the very next row rows amount in this variable and put the at the rate ended also of the you know for the for the same row by which we are going to calculate and we will loop through again and again if this value will not be same then after this loop we will insert value in our has final table at the item amount started and ended so and increase the value of at rate i1 by one so we can see here if we execute this query we will get we will get the result and this is our appropriate result we can copy from here and paste it or excel and we can see we can see these values are same yes so by using this query you can find out this result so i hope you have understand the logic and if you like this video please subscribe my channel and like this video and share as you can thank you so much for watching this video hello guys welcome back my name is susil Singh and today i am going to tell you a very common scenario basically what happened when we create a trigger on a table's update event and inside the trigger we want to update the same table basically we can see here we have already created the table and there is some record exist in this table so we directly put the select so we can see here there is six record in this table you know there is three columns first is id second is name 30 gender and we want to write a trigger by on this table tables update event when we want to update anything in this table then trigger will fire and inside the trigger we want to again update this table so is there any recursive mode by which update will fire again and again or any deadline by which it will be terminated or any breakpoint by which we can see so these are scenarios we can see here basically first need to create the trigger then i am not going to how to create the trigger basically the purpose of the trigger we want to update this table and swap male employees to female and female to male and this trigger has been created i think already created here already created so here we want to put the update and let's see what happened so it has been completed successfully so first is six rows affected second is six rows affected so first six rows affected for this update event second six row affected for this trigger update event so we can say that we can we can say that if we write any we write any trigger inside we update the same table so it fires only once so query is not going to any recursive mode or any loop by which we can directly write the update and put the appropriate result so this is the scenario i have discussed here in my upcoming videos i'll discuss i'll discuss very you know tough scenarios which may help you to get placed in good companies and if you like this video please subscribe my channel and you can find us on my facebook page also and these scripts will be available on on my facebook page if you want to test you can copy from there thank you so much for watching this video hello guys my name is susil Singh and today i am going to tell you about the six rows into question and answers so this is very important question where we have a table and it has only two columns first is name and second is father name so in the name column we can see here susil Singh and his father is ib Singh and then again ib Singh and his father is ib Singh so by using this table we want the output where we want only two columns first is name and second is instead of father name we want grandfather name so how can we achieve it we can see here so by using self join we can achieve this output so what is self join basically self join is a special type of join by which we join a single table as a multiple tables so here we can we can create this input table is multiple times so i have created it for two times first is a and second is b and we can see here so name of susil Singh of this table a and his father is ib Singh of table b so we need to put join between these two tables and first is we can pick the father name of table a and then join the name of table b so we can see here so ib Singh and then ib Singh when this value will match then we want to father name of table b so we can see output would be name is susil Singh and then when this value will be match ib Singh and ib Singh then it will pick the father name of table b is rb Singh so we can see in our output and second when it moved to ib Singh his father is rb Singh and rb Singh will be matched with the name of table b so we can see here rb Singh so rb Singh is matched here so ib Singh's grandfather is rl Singh so we can see here so by using self join we will get the appropriate result we can see here I have already typed the query this is our table so first we need to put the left join because we want all the all the names from the input table so we can see here we have all the names but if grandfather is not available then we want to know data in grandfather name column so we can see here first is we create the multiple tables for the same so this type of join will call as self join so first we want to name of this table a as I have seen here so we can see name of table a and then father name of table b so same I have typed here and if this value will not be available it means father name of table b is not available then we want to output as in no data and we simply give the alias name of this column then put the left join because we want all the names from the from the table a and then put the condition between them father name of table a equals to name of table b and we simply execute this query and we can see here we can copy from here and paste it next here we can see all values are matching correctly so I hope you can understand the logic and if you like this video please subscribe my channel and like the video and you can if you want these scripts it's available on my facebook page you can go through and download from there thank you so much for watching this video hello guys welcome back my name is susan singh and this is continuation of sickle server interview questions and answers so today I am going to tell you about how to reverse any string without using reverse function so let's start this is our code I have declared three variables first is for input string second is for length and third one is for final output string initially I have initialized this final output string as a blank second is I would like to initialize some record on this input string so I have initialized abc space xyz as our input string in this input variable and we want to calculate the length of this input variable so by using length formula I have calculated here so second is we want to use a loop and inside the loop we want to reverse of this string so this loop will be executed until and unless the value of this length formula would be greater than zero inside this loop I have used a sub string function so first of all I would like to explain what is sub string function so sub string function is a string type function and it takes the three input parameters first is for the expression by which we want to get the result second is the starting point by what character we want to start the counting and second what the length we want to get in our output so in this we can see first second so we want to pick the values from b and how many characters we want we want four characters so b c space and v this is the four characters we want in our output so we can see here b c space v so this is our output so inside this loop I have used this formula so first when first time this loop will executed the value of this length would be seven so this is our expression expression is a b c x y space x y z then the length at the length value is seven so it will pick the values from seven and how many character it will pick only one character so we can count in in this input a b c space so seven would be z so and we want only one character so output of this sub string for the first execution would be z and first time at the final value is blank so we want to concatenate blank with z so final value for the first time when this loop will be executed would be z and then we want to decrease the value of this address length variable by one so when the second time this loop will be executed the value of this length would be six it will check the condition condition is satisfied address length is greater than zero so value of this length is six so it will again go inside the loop and when second time go inside the loop the value of this final variable would be z so then sub string expression would be same a b c space x y z the only thing has been changed is length variable length variable is six for the second execution so it will pick the value of y in this input string and it will concatenate z is our final then y of the y of this service string output so final value for after second execution would be z and y then again address length variable has been decreased by one so for the third execution the value of this length variable would be five so value of final would be z and y and we want to concatenate z y plus this service string service string output for the third execution length of this variable would be five so we can see here it's a three four five so five would be x so x is only one character has been picked by this service string and would be x so this we can see this execution of this loop will pick the values from right side and put in the final output variable so on my last execution this all input string has been converted into reverse order so we want to execute this query this is something copy this query and paste into new query window paste the query and execute and we can see the string has been converted into reverse order I hope you have understand the logic how we can use the service string function and how it works so thank you for watching this video this is our query if you want this query this query will all level on my Facebook page if you need hello guys welcome back my name is Susie Singh and this is continuation of SQL Server interview questions and answers so today I am going to discuss about the case statement so what are the case statement this is the syntax of case statement if you want case statement then we have to use case when we can give any condition if this condition will satisfy then value will be written which is given after the case statement if this condition is not satisfied then move to the second condition if the second condition is satisfied then value will be written on the second condition if all these conditions will not be satisfied then we have sblog also here so value will be written which is given in sblog at last we have to use the end so this is our case statement syntax question our first question is what would be the output of below query so this is our query I have copied from here and paste it in SQL Server Management Studio so we can see we have declared a variable at the rate where and value is 10 initially we have initialized the value of at the rate where variable is 10 and this is our case statement basically in this case statement you can see we have put the our first condition when value of add rate where is less or equal to 10 then we want to print condition 1 if this value will not be satisfied then our second condition is if I add the rate where value equals to 10 then we want to print condition 2 if the value of add the rate where is greater or equal to 10 then we want to print condition 3 if all these conditions will not be satisfied then we want to print our else part this is fail all conditions so first I would like to explain how case statement works so so first is this is all first condition if first condition will be satisfied then it will return the value of our first condition and immediate case statement will be terminated it will not go to our second case our third condition it will check only the first condition if first condition will fail then only it move to our second condition if second condition will fail then only move the our next conditions if all these conditions will fail only then it will move to else part so in this condition we can see our condition will be satisfied in first part when add the rate where value is less or equal to 10 then condition one so output would be condition one we can execute and check so this is our output and we can see condition one has displayed here so now move to another question so this is our second question this is our input table and this is our output table in input table we have four columns first is student and second is the subject names like english math and science and we have displayed the marks according to the student so student one has achieved 50 percent and 90 percent in math and 80 percent in science so this is our input table and in our output table we can see student one has the maximum marks in math so subject would be math and maximum marks would be 90 so you know we can see here we want get the maximum marks in the columns not in the rows if we have this condition in rows then we can put simply max and we get our result but this condition is our column so we have to use the case statement for achieve this result so I have already typed the query so this is our sikkosrow management studio and we have created this table as well so this is our table I have all the records and we want to use a case statement by using this case statement we can achieve the appropriate result so this is our first case statement when english marks is greater than math marks if this condition will satisfy then inside this case statement we are going to use another case statement by which we will compare between english marks with science marks if this condition will satisfy english marks is more than math marks then move to our second case statement and in this second case statement we will compare between english and science if english marks is greater than sense marks as well then it will return the english else it will return the science so this is our the first condition so if our you know case one condition will not be satisfied then it moved to else part if math marks is greater than english marks so in this second condition we will check the math between science so if the math marks is greater than science then it will return the math otherwise it will return the science so this is our case statement and inside the case statement we have used another case statement so this is called the nested case statement so we can execute this query and we can see here so we have a student id and we have the maximum marks so here student one have achieved the 90 percent marks in math so this is a 90 in our max marks so we have you know put this value in a temporary table so it's very easy to understand for the subject as well so this is our table and we want subject as well so we have you know put the put the inner join between temporary table this and our input table so when this student id is will be same then we use the one more case statement in this case statement we have compared between maximum marks with the subject marks if the maximum marks is equals to the english subject then our subject would be english if the maximum marks will be equals to the maths then our output would be math else this condition will not be satisfied then in else part we know science would be the maximum so this is our subject so we can execute this query and get the result okay we can use the input table as well so we can compare between them very easily so student id one maximum marks is 90 and the subject is math so in our output we can see student id one maximum marks is 90 and subjected maths for student id two maximum marks in english it's 70 so we can see in our output so this is our table and input table and this is our output query so i hope you have understand the case statement and thank you so much for watching this video if you need this query this queries will available on a phasive page if you need you can copy from there thank you so much for watching this video and please subscribe my channel and like the videos and share as well thank you hello guys welcome back my name is susil Singh and this is continuation of sequence server interview questions and answers so in my previous session i have discussed few questions related to case statement and in this video i am going to discuss one more question related to case statement so this is our question let's assume you have three tables table one table two and table three in table one we have two columns and first column is student id second is education marks and second table we have behavior marks and third table we have sport marks so by using these three tables we want this output so in output we want two columns first is student id and second is grade so how we can achieve the grade so we can see here marks if marks is less or equal to 70 then grade would be fail if marks more than 70 and less than or equal to 80 then it would be pass if marks is more than 80 then it would be merit so by using this we can calculate the grade so how we can achieve this result this is our question if count of merit or pass greater or equal to two then grade would be merit or pass else otherwise all all the cases it would be fail so for the student id one education marks is 70 so grade would be fail behavior marks is 80 so grade would be pass sport marks is 60 so grade would be fail so there is two fails so output would be fail for the student id two education marks is 80 and so it would be pass and behavior marks is 60 so it would be fail and sport marks is 75 so it would be pass so there is two pass so output would be pass so by using these three tables we want achieve this output so let's start this is our sequence of management issues I have already created the tables so this is our table and we have a record on it so let's start with step by step so it's very easy to understand first we want output like you know we have three different tables but we don't want three different tables we want only one table and we want to get a record on you know column wise like student id one then education marks and then again we want sport marks and then again we want behavior marks we don't want in rows we want record on columns so by using inner join we can achieve the result so this is our step one as I have suggested student id and then education marks and then sport marks and then behavior marks so this is our first step by using inner join we can achieve it it's very simple we can join between two tables by using student id so education table and sport table by using student id we can join between them and in behavior we again by using student id we can achieve it in step second so here we can see we have a student id and then education marks and then we can compare education marks with our scenario our first scenario is if marks is less or equal to 70 then status will be fail so here education marks is 70 so first we will compare with greater than 80 if you know value of this education marks is more than 80 then grade of this education marks would be merit if grade you know if the marks is between 70 you know 71 to 80 then grade would be passed so in this is our first condition for the merit this is our second condition for the pass and this is our third condition for the fail so I have repeated this for all three columns this is for for column one and this is for column two and this is for column three we compare we simply compare with our condition this is our condition is less than or equal to 70 then it would be fail if the between 71 to 80 then it pass if the marks is more than 80 then it would be merit so we can compare directly by using case statement so we will reflect the result and we can simply select and we can see okay so in the first condition we can select both of the table so we can compare between them so here education marks so education marks is 70 according to our condition if education if any marks is less or equal to 70 then grade would be fail so it's reflecting fail in the sport marks we can see it's again less or equal to 70 so it again fail behavior marks is 80 so we can see here this is our second condition if marks between 71 to 80 then it would be pass so it's reflecting pass so we have achieved the you know grade by individual now we want you know count between them if the count is more than you know for the second condition we can see pass is coming for two times so output would be grade only once it would be pass for the first case it would be fail for the third condition it's showing two times as a merit so it would be made and in the last it again would be pass so how we can achieve it for the our last and this is our last step and we can insert this record in a temporary table so I have inserted so this is our kind of condition by which we can you know get the appropriate result first we would compare our first we can you know select this so we can understand very easily this is our condition so education we can compare between education marks and sport marks if these two has the same value then output would be you know education marks or sport marks if these two has not similar values then we will compare between education marks to behavior marks if these two has same value then output would be education marks or behavior marks then again if these two conditions would be fail then we would compare between sport marks with behavior marks if these two has the same value then output would be sport marks or behavior marks if these three all the conditions would not be satisfied like we have one merit one fail and one pass so all these three conditions are different so such type of conditions we want output would be fail so we can see we can see here and for the case one we will compare between education marks with sport marks so education marks would be sport marks if this value is equal to then we are going to reflect education marks for the second we would compare education marks with behavior marks if these value would be same then we will reflect education marks if these two condition will not be satisfied then we will move to our third condition sport marks is equal to behavior marks we can see here if these this condition would be satisfied then we will reflect behavior marks or sport marks because these two value would be same if all these condition will not be satisfied then we are going to reflect only fail so this is our output we can select and we can get the result so we can compare between them first is fail second is pass third is merit and fourth is fail so fail pass merit and fail so this is our result I hope you have understand how we can use case statement on several you know phases and we can achieve this such type of result very easily so thank you so much for watching this video if you need this query this would be available on my facebook page you can go there and copy from there please like subscribe and share my channel thank you hello guys welcome back my name is Susee Singh and this is continuation of sequel server interview questions and answers so today I am going to discuss about the like operator so what is like operator like operator is used in ware clause to search for a specified pattern by using wildcards and mostly we use two type of wildcards in like operator one is percentage another is underscore percentage is used for zero to any character and underscore is used for a exact one character and we will discuss later in this video session now let's start so this is our first slide where we have specify few questions our first question is get those records which name start with a so let's move to sequel server manual studio I have created this table and we have five record on this table and we want only those records with you know a student name is start with a so by using like operator we will get the result in this query we can see we have used percentile as I have discussed percentile is used for zero to any character so starting value should be a and end with any character so in this table we can see we have only one row for this condition so by using this query we will get only one row so this is our first question and our second question is get those record which name and with z so for this we can see start with a and now we are going to get the result which name and with z so this time percentile will use for the first and then we use the z as a last character so we can select and we will get the result and our third question is get those records which name start with a and end with z so here we can see we have used a and then percentile and then z so it may be any character between a and z but starting value of this s name should be a and end with z so by using this query we will get the result so this is our first slide let's move to second our next question is get those records which name second position is b as I have discussed underscore is used for the single character so in this query we can see inside the like operator we have used the underscore for the first position so it might be any character so first character would be skipped yeah first character has been skipped from there and the second position is b so we have to specify b from there and then percentile so by using this query we will get the result let's move to sequence our manual studio and execute this query so this is our query we can execute and get the result so our next question is get those records which name contains cd at any position but cd should come together first come c and then d so it might be it would come first position second position at any position so we have to use the percentile at the starting and end and inside the percentile we have to use the cd so by using this query we will get the result another question is get those records so which name start with a and contains at least three characters so as I have discussed underscore is used for the single character so here we have to use at least two underscores for the two characters and one character would be count from a in this query we can see a name like a for the starting position and then we have to specify the underscore and one more underscore and inside the underscores we have to specify percentile it might be any character between you know underscores or a so when we are going to execute this query we can see we will get the result let's move to another question this is get those records which name start with a or m or f when we are going to such type of conditions where it would contain one or more than one characters at the starting position so we have to specify the square bracket and inside the square bracket we have to specify all those characters which may be at the starting position so this is our query and we can execute and we will get the result here we can see a or m or f starting position should be a or m or f so inside the square bracket we have to specify all these three characters so we can see our output another question is get those records so which name doesn't start with a or m or f so we have two queries to get the similar result first we have to use the above query for the same and except we are using like we have to use the not like here we execute and we will get the result our second question is we have to use this operator I don't know the name of this operator so we have to specify this operator before we are going to specify our characters and then execute and we will get the same result so we can execute these two queries at once and we will see it would return two rows for the first query and two rows for the second query so output would be same our next question is get those records which name start with a and you know start with the between a and m so it might be a b c d it may be anything but it would be between a to m when we are going to get such type of condition so we can specify a b c d f g h up to m inside the square bracket but this is not the proper way so we can see we have used dash inside the square bracket and between a to m when we are going to specify this dash it will act as a between operator so we execute this query and we can see we will get the result now let's move to another question so another question is get those records which name contains the percentile as I have discussed percentile is used for the zero to any character but we want those you know those values which name would contain percentile like let's move to sequence of management studio and here we can see in this we have student id 6 and value is s name is x y x and then percentile and tt so write the query which will return such type of conditions so here we have to use the square bracket and inside the square bracket we can use we can pass the percentile and it will reflect the result so for the same if we are we are going to get the result which will contain the underscore we will use the same query and we will get the result first we need to insert some value so we can insert and then we can select and we can see here it will reflect the result so I hope you have enjoyed this video if you like this video please subscribe our channel for many more videos if you need this script it will all level on my facebook page you can go through over there and copy from there thank you so much for watching this hello friends welcome back my name is Susheel Singh and this is continuation of sequel server intro questions and answers so today I am going to discuss one more question and this question is related to city and row number here we can understand the logic behind row number and city so let's start so here we have a input table and this table is related to phone number you can see it contains five columns first is id then the phone type then primary then sequence and then the phone number so so here we have output table and in this output table we have four columns first is id second is business phone number so business phone number is related to when type in input table is b and then the home phone number when type is h and then cell phone number when type is c so this is our output table and this is our input table so we can see we have to pivot this input table to get the output table but the condition is we want those phone numbers which primary is one so here we can see we have primary column and then the sequence columns all logic behind the primary and sequence and then we want to pivot so there is three logics first when primary value is one then we want pick that row as it is and we want not we are not going to see another row related to the same type and id and then if the primary value is not equal to one then we will see the sequence number and pick the maximum sequence number for that row and we want display in our output so simply we can see for the id one we have only a single column so this is for business phone number so one and the number is one so id is one and the business phone number value is one and rest is not available then move to the id two in id two we have business we sorry we have cell phone number and we have home phone number cell phone number we have a single row so we will display that record as it is and the number would be two so here we can see business phone number is not available for the id two and we have the home phone number and home phone number first is a cell phone number so cell phone number value is two and here value is two so this is our output and move to the home phone number we have two rows for the home phone number first we will check the primary so primary value is one we can find out from here so we will display three in our home phone number so in our output we can see and then move to the id three in id three we have all three columns book phone number sorry business phone number home phone number and cell phone number so business phone number we have a single row so five will be displayed as it is and then move to the home phone number and in home phone number we can see we have primary value is one then this row number is six will display in our output and then move to the cell phone number in cell phone number we can see we have two rows first is where primary value is zero and second we have primary value is zero so primary value is zero for these two rows so we have to move the sequence and pick the max sequence number that is two we will display in our output nine so we can see here so this is our input and this is off let's move to SQL Server Management Studio to write the query so this is our SQL Server and I have already created this table which is phone master and we can execute this table and this is our input data so this input data and I have displayed in slide so these two data are same so now we write a query by which we want to display this output okay so you know there is several steps to perform this task so first is our row number so try to understand one by one so it's very easy to understand in row number I am going to use partition by on the behalf of ID and primary type so I am not going to discuss what is row number if you want then go to my SQL Server tutorials and you can see from there now you can see I am going to use partition by on the behalf of ID and type and then I am going to use order by in its primary column in decreasing order and in then sequence number in decreasing order and its primary value might be null so we are going to use is null and if value is null then it will be replaced by zero then it will be sought in decreasing order if one is available then it will come first then values come for zero and other and second if the value is same for two rows then it will move to the sequence number and sought in sequencing order so according to our requirement we can see if the value of primary is not null sorry not one then pick the max sequence number so by using this condition we can pick we can execute this query and now we can see we want only those rows which row number value is one so we can filter from here where row number is equals to one so this is our intermediate input table so this is our first step now moved to our second step so in our second step I am going to use the pivot basically in our input table we can see we have to use these rows as a columns so we have to use the pivot so here we can see we have used case statement so if you haven't watched my previous session please go and watch then you can very easily understand what is case statement and how it works so here you can see this is our selected statement and we have used one more cte and inside this second cte I am going to use this first cte so in this first cte as we have displayed the row number one we are going to pick the values of row number one only so then if the value of phone type is b then it will return the phone number for the business second if the value is h then it will return phone number for the home second is c then it will return the phone number for cell phone number if I am going to execute this query then you can see it will return the result all these four columns which we want in our output but the values are not perfect as we can see here we want ids only once but here ids are going to repeat more than once so we have to use one more logic by which we can simplify it and provide output as we want so I have already typed the query so copy from here and I am going to paste it here and now we can execute column name should be business phone number so it's a business phone number now execute and now we can see the our output so it seems very good as we want so the this output and this output is very good so logic behind it I am going to use the group by on the behalf of id and then I am going to put the maximum business number and then maximum home number and then the maximum cell number because you know there is two rows one value is null another value is something then it will pick the max value max value should be where we have some value null value is not going to reflect over there so it this problem has been reduced by using max and we don't want to display null then we have to use is null and we replace the null value by n a so this is our output as we want so I hope you have understand this logic and it's very important if you like this video please subscribe our channel for many more videos if you need this script it will be all level on my facebook page you can go there and copy thank you hello guys welcome back my name is susil Singh and this is continuation of sequence of our interview questions and answers in this session you will understand how joints work and second is team matches so team matches is our interview question so directly move to our question so you can see we have input table and it contains two columns first is id second is team so id has four rows and team also so in team you can see India Australia Sri Lanka and Pakistan so in output we want only one column and here so in our input table teams will play with other teams like India will play with Australia Sri Lanka and Pakistan and then again Australia will play with India Sri Lanka and Pakistan so in our output each team will play with other teams like India will play with Australia so this is our first row India will play with Sri Lanka so this is our second row India will play with Pakistan so this is our third row so now Australia we can see Australia has played with India so this row will be skipped and then Australia will play with Sri Lanka so this is our fourth row Australia will play with Pakistan so this is our fifth row now Sri Lanka will play with Pakistan only because slanka has played with australia and india so when slanka will play with pakistan for our last row and then we can see pakistan has played with india australia and slanka so we can see each teams has played with other teams so this is our input input table and we want this output so how we can achieve this so first we need to understand how joints work so here you can see we have a input table and we are going to use as a self-join so we split this table into two parts for t1 and t2 so t1 our left table so t our left table and t1 is our right table so when we are going to join between them it will pick the first row from left table and it joined from right table so id team id 1 will join with t1 team id 1 so condition will not be satisfied then this row will be skipped and then move to our second row so second row we can see id is 1 and then id is 2 so we can see here tid and t1 id so value of tid is 1 and value of t1 id is 2 so in our output we can see it will reflect india and then versus t1 name is australia so it will reflect this output and then again team id 1 will check with slanka so our condition will satisfy so t1 name is india and then versus and then we can see slanka and next id is 1 and id t1 id for pakistan is 4 so our condition will satisfy then we can see india and then versus pakistan so our condition will satisfy for all these rows once this value will be completed then it will move to our second row and it will try again check from starting to end and join between them so two will check from one so condition will not be satisfied then it will move to our second condition will not be satisfied for the row number three id is three is greater than id is two so it will reflect australia for our left table and then versus then slanka from our right table for pakistan condition will satisfy then australia and then versus then pakistan so for the our third row only this condition will satisfy slanka and pakistan so we can see slanka versus pakistan so this is our output and we want this output as we can see here so this is our output and we can get this output by using self join in this input table and we are going to put condition between team id when team id from left table is less than team id from right table so let's move to sequence row management to studio i have already created this table we can select this table we can see here we have two rows one two three four and then india australia slanka and pakistan we are going to put inner join between them and we can see in excel we are going to join between t and t1 by using team id when team id from left table is less than team id from right table we did the same thing here we on condition and then t team id is less than t1 team id so let's execute this query and we can see we are getting the same output as we can see in excel so copy and paste here and here you can see india versus australia india versus australia india versus slanka india versus slanka and then india versus pakistan india versus pakistan australia versus slanka australia versus slanka and all these rows are same so we can achieve this output by using this query i hope you have understand this video and thank you so much for watching this video if you like this video please subscribe our channel for many more videos if you want this script these will be available on my facebook page you can compute from there thank you so much hello guys welcome back my name is sushil Singh and this is continuation of sequence for our interview questions and answers in this session i am going to discuss how we can apply join in a single cell so let's move to our question this is our question we have two input tables first is item master and second is variant master in item master we have two columns first is item code and second is variant id in variant id you can see you don't have normalized data so you have comma separated values in variant id in variant master we have normalized data and it has only two columns first is variant id and second is variant description in our output we want to put join between variant id of item master and variant id of variant master and then we want to display our output in our output we can see simply we have to pick the values for item code and then we will pick the value for variant description from variant master so here we can see variant id one so variant id one variant description is for variant id one is a so we have to display a as it is and second is two so value for two is b so b will be displayed as it is so for item code two variant id values is 3 comma 4 so 3 is c and 4 is d so for two c comma d will be our output now let's move to SQL Server Management Studio where I have already created these tables so our first table is item master and it has same metadata as I have displayed in slide so this metadata and this metadata both are same and I have created second table as well which is variant master and we can see it has the same metadata and before going forward I would like to explain about the care index how care index work so care index will return the position of any character with a within extreme so our input string is suseel comma sync and we want to get the position of comma so we can simply execute and we can see it will return the first occurrence of this comma of this comma when we are going to execute then we can see it will return it so we can count from s so we can see comma is in eighth position so it will return it inside the query so you can see this is our query by which we can get our output so this is our output and before going forward I would like to select item master so in item master first of all I want to split variant id one and then two we want to split these id's in separate column and these id's would be separated by using this comma so here inside the city I have already write the code by which we can see it will return our desired output so here you can see in our first left value from this comma we are going to use the left function and inside the left function we want pick the value from this variant id column and then how many characters we want we want pick only those characters which is before this comma so comma will be written by using this care index as I have explained earlier so it will return the exact position where comma exist then I have to subtract minus one by this care index value because it will return value where comma exist so we want value before comma so it will return the value for left join and second I am going to use substring so in this substring we are going to select value from this variant id column and we want to pick the value where this comma will exist it will return our first occurrence of this comma and we want pick the value just after this comma so we have to add one from this care index and then how many characters we want then we can calculate the total length of this cell and then we have to subtract the comma value so we can simply execute our length function then we can subtract comma then we will get our result so this is our result we can execute and we can see this is our desired output so we have to simply put join between this table with our variant master table and then we are going to combine the output for these two tables so here you can see I am going to use left join between this cte and this variant master table on the behalf of first of all left join column and second for right join column so first we return the values for before this comma and second we return after the comma so once I am going to execute then we can see it will return our desired output as we have seen here so both outputs are same you can use the query and we can get the result I hope you have enjoyed this video if you like this video please subscribe our channel for many more videos if you need these scripts will available on my facebook page you can copy from there thank you so much for watching this video hello guys welcome back my name is Sushil Singh and this is continuation of SQL Server interview questions and answers so today I am going to discuss one more question which is related to interviews so in this question you will understand add only integer integer values from any string so this is our question you have a variable and in this variable you have some string values like you can see here this is our input and we want to add only numeric field in our output so we can add one then three then four eight nine and one and you will see you will return 26 as an output so let's move to SQL Server Management Studio where you will understand how we can do this so here I have declared four variables variable one which is worker type and second is at the length one and then final last variable is at the rate t so in the first variable at the rate where one I have in a slice the input string which is at the rate at the rate one has something like that and then I am going to calculate the length of this input string so it will return the length by using length function and I am going to initialize in at the rate length one variable so here I am going to use a loop by which this loop will not be and until the value of this at the rate length one variable is greater than zero once value of this is equals to zero then this loop will be and so here you can see I am going to pick the first value from left hand side from our input string so it's at the rate so here I am going to pick this at the rate by using left function and it will initialize in at the rate t variable and then I am going to check this is numeric or not if value of this is numeric then I am going to execute this begin and end otherwise it will go to our second condition which is set at the rate where one is equals to it's going to pick the value from right hand side after skipping this first letter by which we will get at the rate one has three in our at the rate where one variable here then I am going to set at the rate one minus one so here you can see this loop will go once more and next time it will pick second at the rate and it will executed as it is and third time once value will pick by one then our if condition will be satisfied then it will go inside and here I am going to add in this final variable by this at the rate t variable so finally it will add one once it will reach three then our if condition will be satisfied and it will add three on our one so value for at the rate final variable after three is four so this loop will be executed until this string will not be completed and at last you will get the result and result output would be 26 as I have discussed discussed at starting so it's 26 so I hope you have understand how you can achieve this output by using loop so thank you so much for watching this video if you like this video please subscribe our channel for many more videos if you need this script it will available on my Facebook page if you need you can copy from there thank you so much hello friends welcome back my name is Suse Singh and this is continuation of SQL Server interview questions and answers so today I am going to discuss one more question which is related to interviews in this session I am going to discuss how we can add only integer values from any string when values are in more than one digit so in our previous session I have discussed how we can add values when integer values in our single digit but in this session we will understand how we can add the values when they are existing more than one digit so let's move to another slide where we will understand what it means so this is our slide so here you can see we have an input string and we have output in our input string you can see you will start from left hand side so our first numeric value would be one and then our another numeric value is three when we move from left to right then you will get four and then two so four and two are coming together so when we are going to concatenate between four and two then we will get 42 and this 42 is a numeric so it will treated as a single unit then again we are going to move and here you can see we are going to see 181 when we are going to combine these three values it will 181 so it will treated as a single unit and then nine and at last you will get 10 so this is our input string when we are going to add numeric values from our input string then you will get 246 as in output so how we can achieve in SQL server so let's move to SQL server management studio where we will understand how we can achieve this so I have already typed the code so you will understand very easily and we can save the time so I have declared five variables our first variable as a input variable which is where one so this is our variable and this is our input string so I have initialized our input string in our address variable and then length variable in length one variable I am going to calculate the length of this our input string so this will be initialize in our length one variable so now I am going to use a loop so here this loop will be executed until unless the value of this at the rate length one variable is greater than zero so inside this loop I am going to fetch the value from left hand side of this input variable so this input variable has at the rate in our left hand side so first value will be picked from here and this value will be in slides in our at the rate T variable so this value it will be in slides in at rate T variable then again we are going to check inside this loop if this value is numeric or not when this at the rate T variable value is numeric then it will move inside this if block otherwise it will move in s block so in this condition it will move in s block so inside this s block you have set the value of this final variable is equals to at the rate final and then I am going to add the value of this at the rate inter variable by default it's blank when we are going to convert in our integer then it will return zero because there is no value existing at the rate inter variable and then again I am going to initialize this value by blank so this s block will be finished here and when this s block will be finished I am going to use this at the rate where one and I am going to remove over this you know left hand side first variable and then again initialize this value in our at the rate or one variable so in where one variable I am going to remove the first value from left hand side because I have used this at the rate in our first execution for another variable is at the rate length one variable I am going to decrease the value by one so for next time it will pick the value because our first at the rate has been finished and then it will move over second at the rate so it will again go to our s block when this s block will be executed as a same and final value will be same for the second time so when these two value will be skipped then it will move over third third character is one and it will be executed as the numeric so for third time it will insert in our inter variable so in inter variable at the rate t will be concatenate with at the rate inter variable so at the rate inter variable is blank for our first time and this is one so this is one so this s block will be skipped and then move to our next so here it will one will be skipped and then move to at this has symbol so this has symbol will be picked for fourth time and once it will be picked then it will be checked whether this as is numeric or not so we know this value is not numeric so moved to our s block so inside this s block at the rate final has zero at this time so at the rate final will be zero and I am going to use this inter variable so inter variable value is one as I have discussed so we can convert in our integer and we can simply add with at the rate final and then I am going to set this inter variable as a blank execution will be finished when it will move to four then it will be checked and initialize four in our inter variable and then we will move to two then it will again face the value for two and it will be is numeric is true so it will be concatenate between four and two so it will be initialized 42 in our inter variable and then equals to then this condition will not be satisfied then it will move to s block so inside this s block we can see at the rate final one is equals to address final one plus I am going to add this inter variable value with this final so it will be added so it will be executed for all the execution and we will get the result so once we will executed we can see we are going to get value of 236 but in our slide we can see we want 246 when we are going to add these all these values so why this happened because this integer value is exist at the last position so we don't have any non-character value after the integer value so how we can remove this condition in our code we have already typed the query so I am going to uncomment this code so here for the last execution I am going to check whether this inter variable value is numeric or not once this value will be find as a numeric then I am going to add this inter variable value in our final and we will get the final value so it will be checked for last condition as well so now we can execute and we can see we will get the appropriate result as we want so here you can see 246 and in our code we have achieved 246 so I hope you have understand how we can achieve this output by using SQL Server if you like this video please subscribe our channel for many more videos if you need this script it will be available on my Facebook page you can copy from there thank you so much for watching hey guys welcome back my name is Susheel Singh and this is continuation of SQL Server interview questions and answers so today I am going to discuss one more question which is related to interviews and this question is totally related to attendance so let's move to another slide so in our second slide you can see we have an input table and it contains four columns first column is employee ID second is employee name and third is working day and fourth is attendance so any employee which is working in any organization is work for any day so here you can see employee ID one employee name is join and working day is 15 3 and 18 so on this day join attendance is present for another day where is 16 3 and 18 and join is also present and for the 17th 3 and 18 you can see join is absent for another employee which is Howard so 15 3 18 Howard was present but 16 and 17 Howard was absent so you can say we want output where it has four columns first is employee ID second is employee name and third is present another is absent so we want to split these attendance in rows two columns so how we can achieve this output by using SQL Server so let's move to SQL Server Management Studio where we will understand how we can achieve this output so this is our SQL Server Management Studio and I am using SQL Server 2014 so here I have already typed the query so it will save the time so here I have written create table and table name is employee attendance tbl and then it has four columns as I have discussed in slide so it has primary key which is composite primary key and it is on employee ID and working day so here you can see we can directly execute this query to create the table once we have executed this query table has been created successfully so now I am going to insert the record on this table so we have already typed the query so we can directly select and we can see the output so this is our output say we can match this output with our slide so this input table and this output is same so we can directly insert to our table so we can execute this query this query has been executed successfully and six row affected so six row has been inserted into our table now we can select and we can see this is our table and table has been created successfully with input data now we need to write the query by which we will get the appropriate result so we need to write select then from and this is our table so we have to write the table name so we can copy from there and we can paste it here so now we want only four columns first is employee ID second is employee name so we can simply write EMP ID as it is and then EMP name and then we want present and absent so here this is our input table and in input table you can see present and absent both are present in rows so we need to split these rows in our columns so how we can do we have to use the case so now case and then when now I am going to check where attendance is equals to present so if this value is present then I am going to return one else I am going to result zero so this is our end so as we can say this is our present so we can write the alias name for the same and this is present now we can execute this query and we can see it's going to we execute these queries at a single time so we can compare between them so this is employee ID one where employee name and the present so present is one so we can see for join we have two present so here it's going to result two present and one absent so it will return zero so we can copy this case statement and execute this case statement for the absent and here we can remove this present from absent so and we also need to remove this alias name from present to absent so we can do this and now we can execute this query and we will get the result now we don't want to see the data as we have displayed here we want to summarize the data so we have to use group by and we can use the sum for these present and as well as absent so we have to write sum for these two and now we have to use group by on the behalf of employee ID and employee name so we need to write group by and employee ID and employee name now we can execute and here we can see we are going to get the result as I have displayed in slide so this is our slide we are getting employee ID one which is join and present is two and absent is one so in our output we can see join present is two absent is one so data has been matched now we need to write the order by so we have to write order by on the behalf of employee ID so we will get the same result as I have displayed 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 available on my Facebook page you can copy from there thank you so much for watching hello friends welcome to SSU detect my name is Susil 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 by 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 joint and second is Howard so joint salary is 50 000 and Howard salary 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 another 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 am going to drop it has been dropped now I am 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 areas name is e and then I am going to put inner join between these two tables then department tbl 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 id 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 alias 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 for 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 enable on my Facebook page you can copy from there thank you so much for watching hello friends welcome to SS Unitec my name is Susheel Singh and this is continuation of SQL Server interview questions and answers so in this session we will understand how we can get top-to-maximum salary with employees detail in each department let's assume you have more than one departments in your organization so first is IT department and second is HR department so we want to get those employees which salary is maximum in his department or second maximum so let's move to another slide where we will understand what it means and how we can calculate so here we can see we have two input tables firstly related to employee information and second is related to department information in employee information we can see we have four input columns first is employee ID second is name then salary and then department ID and in department information table we have only two columns first is department ID and second is department name we want to get our output where we want four columns first is ID second name then salary and then department name as we can see here department name is available in department table and rest of the information ID name and salary is available in employee information here we can see in department ID one we have department name is IT so in IT department we have three employees first is John second Sunil and then Subhangi and we can see John salary 40,000 Sunil salary 40,000 and Subhangi salary is 45,000 so as we can see Subhangi has the maximum salary in IT department so we want to display Subhangi's record as in output so in Sunil and John we can see these two has the same number of salary so these two has 40,000 and 40,000 we can see they are getting second highest salary so we have to display all these two employees in our output as we can see here we have displayed John and then Sunil let's move to another department department ID two which is HR department so in HR department we have three employees Pankaj, Mary and Deepak so Pankaj's salary is 80,000 and Mary's salary is 55,000 and Deepak's salary is 75,000 so we can see Pankaj is getting the maximum number of salary in HR department so his salary is 80,000 so we want to display in our output as it is and then between Mary and Deepak Deepak is getting 75,000 so we have to display Deepak in our output so this is our output so how we can calculate this output by using SQL Server so let's move to SQL Server Management Studio where we will understand how we can calculate so as I am using SQL 2014 I have already created these two tables which is employee and department I am going to execute so here we can see this is our employee ID employee name salary and department and it has the six rows in our slide we can see we have four columns and they have six rows so all the metadata which is available in slide same as we have inserted in our table so if you need this script it will our level on my Facebook page you can copy from there so now I am going to use first of all you can see in our slide we have four columns in our output first is ID then name then salary and then department name so we have to put inner join between department and employee table to get the department name so first of all I am going to put inner join where we will get the department name so select and then employee table so this is employee table I am going to put alias it is then inner join with department table so this is our department table and I am going to put alias is D and we are going to put join on the behalf of department ID so this is our D dot department ID is equals to E dot department ID where this ID will be equal then we want to display our output so in output we want to display employee ID then employee name then employee salary so all these information is available in our employee table and we want to display department name instead of department ID so department name is available in department table so we can calculate from department table now I am going to execute and we can see all the columns is available as we have displayed in slide it has employee ID name salary and department name so here we can see employee ID employee name salary and department name so all the columns are available over here so now I am going to calculate the maximum and second maximum salary in each department we have to apply dense rank by which we can assign some numbers and then we can simply filter on these numbers so how we can apply dense rank we have to put dense rank so this is our dense rank then we have to use over clause this is our syntax for dense rank order by clause is mandatory so we have to order by on the behalf of salary and then we want to put in decreasing order and now I am going to execute and we can see what would be the output so this is our output so Pankaj has the maximum salary in whole table so it comes first then Deepak has the second highest salary so it is over second Mary has the third highest salary so it is over third so Bangi has the fourth then it four and then John and Sunil has the same number of salary which is in fifth position but we don't want to calculate in this way we want to partition by in department so in department we can see we have to apply partition by on the behalf of department ID so before order by we have to use partition by so this is our partition by partition by on the behalf of department ID so department ID is available in these two tables so we have to put alias name by which we can see it will not reflect any error now I am going to execute and we can see it's going to partition on the behalf of department ID so this is our one partition which is department ID is one and then department ID is two then it's going to again partition so we can see we have one and two so first is for the maximum salary second is for second highest salary and in HR department we are going to get one two and three so 80 000 is the maximum salary then Deepak has the second highest salary and then Mary has the third highest salary in the HR department but we want to display only these two records so we can simply put filter and we can get the result so now I'm going to use a CTE and within CTE it will be calculated so CTE temp and then as then we can start a bracket and then we have to close the bracket and here we can see we are going to select all the records from this CTE table and now we can see we will get the same output as we have displayed but we don't want to those records which RW value is not equals to one or two so we want to pick only one and two so now we can see we are going to get as we want in our output we don't want RW so we have to assign the column name which is employee ID employee ID then employee name then salary then department name so this is our department name now we can execute and we can see we are going to get five rows and four columns so here we have five rows and four columns ID three one two four and six here three one two four and six so we can see we are going to get result as we have displayed in our slide so I hope you have understand how we can calculate if you like this video please subscribe our channel and share the video as well if you need this script it will be available on my Facebook page you can copy from there thank you so much for watching hello friends welcome to SS Unitec my name is Suseen Singh and this is continuation of SQL Server interview questions and answers so today I am going to discuss how we can calculate the current value previous value and next value without using any predefined function which is our level in SQL Server by which we can directly calculate the values for next 10 years let's move to another slide where we will discuss what I want to say so this is our slide and you can see we have an input table and it contains only two columns first is customer ID and second is customer name by using this table we want our output and output we want four columns first is customer ID and second is customer name which is available in our input table and we want to calculate two additional columns first is for previous name and second is for next name in previous name we can see for customer monies we don't have any previous value for this customer so we have to display and a a value is not available and let's move to Deepak in case of Deepak we can see previous customer is available which is money so we want to display monies in our previous name column for monies we have Deepak is our next value so we have to display the Deepak in next name column so this is our input and this is our output table so by using this input we want to calculate this output before going to SQL Server Management Studio let's move to Excel where we will discuss the logic how we can calculate the previous and next name values so this is our Excel and you can see this is our input table and we want to this output so we have to apply self-join in this input table so let's move to copy and I want to paste here and again I want to paste here and we want to apply left join by which we want to all the columns which is available in our input table we want to display in our output so we have to apply left join between these two tables so this is our table one and this is our table two so here we need to apply the join on the behalf of ID so let's increase ID by one so we want to add one on this table two ID so here I want to add one and then we can scroll so values will be automatically generated so here we can see now I want to apply a join between these two tables we want to calculate the all the values which is available in our left table and we want to calculate the values when this ID which is newly generated ID which is an ID we can say this column is newly generated ID so this is our NID we want to apply a join on the behalf of T1.ID and T2.NID in the case of monies we don't have any values so it will return null for the ID one when we move to our second customer which is Deepak so in case of Deepak Deepak's ID is two so we can see NID two is available over there so it will pick the customer name so customer name is monies so monies will be displayed here so this is monies and let's move to another customer which is Vipul so Vipul's customer ID is three so three is also available in NID and customer name is Deepak so Deepak will be displayed so this is our Deepak so as so on we can face the values from there so this column is our previous value so finally we have calculated the value for previous name now move to next name so here we can see we have calculated the value for NID and we have added one on the ID so when we are going to calculate the value for next name we have to subtract by one so one minus one this value should be zero and we can scroll and we can see value has been calculated automatically we need to add one more column where we will calculate the value for next name so this column should be reflected for next name when we are going to put left join between T1 and T2 on the behalf of ID so for the monies ID is one and when we move to NID in T2 table then we can see it reflect for Deepak so next name value should be Deepak will be reflected over here so this value would be Deepak and when we move to our second customer which is Deepak then we can see its ID is two when we move to T2 table then we can see ID2 is also available over there and the value is Vipul so Vipul will be copied so as we can see we have calculated the value for the same and we can use the same logic by which we can get the previous and next value so now let's move to SQL Server Management Studio where we will write the query by which we can calculate the output so this is our SQL Server and I am using 204 version of SQL Server this is our script to create any table table has been created successfully and I am going to insert the record as I have displayed in our slide six rows affected so value has been inserted successfully in our table we can select and we can get the input table as we have displayed in our slide so this is our slide we have six rows and we have the same metadata as I have displayed in slide as I have displayed we have to put left join in same table then we have to write left join and then we have to use this table and I am going to put a alias name which is for first I am going to use for previous and this is our current and on the behalf of id so this is our current dot id should be equals to previous table dot id and when we are going to calculate the previous value then we have to add one in our previous id as I have displayed in excel so here we can see when we are going to calculate for an id when we are dealing with previous name then an id has been calculated when we add on this id by one so here we have added and now I am going to execute this query so here we can see we have get the data so we want to get all the information which is available in our left table which is current value table and we need to calculate the value for right table only for name so previous dot name and now I am going to execute this query so there is one null record and then we can see this is our previous value and Deepak is our previous value we pull it previous so all the data is reflected as we want so we have to use is null for replace this null value and we have to specify a name and this is our previous name or we can say this is our pname so previous name has been calculated successfully now I am going to put one more left join on this result set with the same table and it will calculate the value for next this is our alias name and we are going to apply the condition current dot id is equals to next dot id should be minus one as I have displayed as I have explained in excel when we subtract minus one in our id then we can calculate the n id so it has been successfully reflected and now I am going to copy this code and paste it here and now I don't want to get the name from previous this time I want to get the name from next and this is our next now I am going to execute this query once we execute this query our output is same as we want in our slide so this is our slide so this metadata and this metadata both are same so I hope you have understood how we can calculate the values by using SQL Server query so thank you so much for watching this video if you like this video please subscribe our channel for many more videos hello friends welcome to SS Unitec my name is Susil Singh and this is continuation of SQL Server interview questions and answers so today I am going to discuss how we can delete duplicate records when duplicate values are based on multiple columns in many articles you have seen how we can delete duplicates but on these articles you have seen the duplicate records are based on a single column but here we have totally different scenario here we have duplicates but these duplicate are based on more than one columns so let's move to another slide where we will discuss about the question so this is our slide as you can see we have an input table and it contains three columns first is from location then to location and then the distance between these two locations as you can see in our first row we have Delhi to our from location and then to location is Mumbai so distance between Delhi to Mumbai is 1415 so it has been displayed here so again we have from location is Mumbai and to location is Delhi so the distance between Mumbai to Delhi is 1415 as you have seen distance between Delhi to Mumbai is 1415 so these two records treated as a duplicate Delhi to Mumbai and then Mumbai to Delhi so we want to delete one record between these two records and we want to display only one record in our output as you can see I have deleted Mumbai to Delhi record in our output and I want to display only Delhi to Mumbai in our output and the rest of the records are distinct records so we want to display as it is in our output so how we can delete such type of duplicates so let's move to SQL Server Management Studio where we will understand how we can write the query and get the output so I am using SQL 2014 you may have another versions but the query would be same for all the versions so I have already typed the query I want to execute so table has been created successfully and then I want to insert some records so record has been inserted five rows affected and then I am going to check the record so here we can see we have from location and then to location and distance between these two so here we can see we have five rows and in our slide we have five rows so this metadata and this metadata both are same so now how we can delete these duplicates records first is Delhi to Mumbai and then Mumbai to Delhi so I want to delete one record between these two duplicates so first of all if we have these duplicates values in our single column then we can use ranking function and then we can delete the duplicate so how we can calculate and put these values in a single column so first of all I am going to write the query select from then the table name and this time I want to select all the rows so select start from table name so I am going to use a case statement case when then the value of this from column is greater than two column then I want to merge between from column to two column so and so this time if the value of this from column is more than two column then I want to concatenate between from column to two column so this time execute and we can see Mumbai is greater than Delhi so it's Mumbai Delhi Mumbai is greater than two Mexico then Mumbai Mexico and here Vietnam Thailand and here we can put one more condition if this value is not satisfied then what we want to display so we want to concatenate between two column to from column so this is our form column now I am going to execute so here we can see we have Mumbai Delhi and we have Mumbai Delhi so these two values treated as a duplicate records and it's come a single row so we can simply put a rank function and we can delete the duplicate so this time I am going to use as a column and now I am going to put this value in a drive table so this is our drive table and I am going to put as a alias them a and here I am going to write the selected statement select from this a and here I want all the rows and I am going to use row number for the ranking so row number over and then partition by so I am going to put a partition by on the behalf of this newly created column and order by on the behalf of this column and as a row number so here we can see so we have one then two then one one one so here we can use a city or we can use one more drive table so I am going to use one more drive table and name is b and then select from from this table and where this row number value is equals to one and now I want to execute and we can see we have four rows Delhi to Mumbai Mumbai to Mexico Delhi to US and with time to Thailand so in our slide we can see we have Delhi to Mumbai Delhi to US Mumbai to Mexico and Vietnam to Thailand so all the four rows had been presented here by using this query we can simply calculate the output as you want and we can remove our duplicate values so I hope you have understand how we can achieve this output by using our query so thank you so much for watching this video if you like this video please subscribe our channel and press bell icon so you will get all the notifications hello friends welcome to SSUnited my name is Sushil Singh and this is continuation of SQL Server interview questions and answers so today I want to discuss one more question which is related to interviews so this is status failure report so let's move to another slide where we will discuss about the question so this is our slide and you can see we have input table and it contains two columns first is transaction ID and second is status and we can see status value is P and F when it comes feed then pass otherwise fail so we want to get those transaction ID which comes after the status pass so here we can see we have transaction ID one status value is P and then transaction ID is two value is F so F come after the status pass so we want to get in our output as you can see we have transaction ID 10 and value is fail our previous transaction before the 10 is 5 and the status value is pass so we want to get in our output so such type of values we want to get in our output which comes after the status pass so how we can achieve this output by using SQL Server query so let's move to SQL Server management studio where we will try to carry to get the output as I am using SQL 2014 you may have another version of SQL Server but the query would be same for all the versions doesn't matter what version you are using and I have already created the table and inserted the record on the same so here we can see we have transaction ID one status is pass and another transaction ID is two and a status is fail so two would become in our output so how we can achieve this output so first of all we need to understand the logic how we can achieve this as you can see in our input table we have transaction ID but transaction ID not in sequence as you can see transaction ID is five then there is some missing transaction ID is if we have transaction ID in a sequence then we can apply a self join and we can get the output so first of all we need to create a sequence number for creating sequence number we have to use row number so this is our row number and we have to apply over clause and then we can use order by on the behalf of this transaction ID so this is transaction ID and now we can execute and we can see we have a column and it has the sequence number one two three four five like this and we can do some alias name is sequence so now let's assume you have the sequence number as a transaction ID so copy this and go to excel to understand the logic how we can get the output so this is our excel I want to put self join between these two tables so I have pasted two times so here we can see here we can delete this row and this transaction ID as well so and now this is our transaction ID when status value is p then we will get in our single table like one four five and then we want to apply a join between this and this table and in this table we want to get only those status which is past so here we can see we have one four five and then eight so now I want to apply join between these two on the behalf of sequence number so this sequence number value is one is equals to and here we can see the sequence number value is two so we have to compare between our first row from this past status table and the second row from this and a table so we have to apply this sequence number from left table plus one so sequence left plus one is equals to sequence right table so when we apply such type of join then we can see we will get the output so now let's move to sequence row management issue to apply join so now I want to put this query in a city so we can call the city as a city main and as keyword so this is the syntax for the city and now I want to use one more city to get our left table as I have displayed here and in this left table we want to get only those transaction ID which status is past so we can put simply comma and we can use simply nested city and give the city name so city pass as we are going to get only those transaction ID which status is past so select stick from city main table city main and as keyword missing where status value is past and then we can see select a stick from city pass when we execute this query we will get those transaction ID which status is past so now we have to apply join between city pass and city main so this is our city main and we want to put alias as a then inner join with city pass as p on a dot sequence number is equals to b dot sequence number as we have explained here our left table is those table which status is past so we have to add one in our sequence so this is our b table so we have to add plus one and we want to get only values from main table and we have to apply a where clause because we want to get those transaction ID which status is fail so a dot status value should be fail and now I want to execute this query and we can see we are going to get the output as we want so I want to select the table so it's very easy to compare between them execute and I am going to maximize so here we can see little bit big yeah so here we can see we have two ten and two zero two so two value is fail so here we can see our first value is past that is okay then ten so our previous value is five and status value is past then two zero two so our previous value is past so by using this query we can get the output so I hope you have understand how we can get the output if you like this video please subscribe our channel for many more videos and press bell icon to get all the notifications for new videos if you need this script it will be available on my facebook page you can copy from there thank you so much for watching hello friends welcome to assist unit my name is susil Singh and this is continuation of SQL server interview questions and answers so today I am going to discuss a very common question generally interviewer ask this question to us what is the real time use of function or stop procedure if we have stop procedure in our SQL server then what is the requirement for the function we can done all these things which can be possible in function by using stop procedure then what is the scenario by which we can use function and we cannot use stop procedure so let's move to another slide where I have created a scenario by which we can get the output by using function but we cannot get the output by using stop procedure so this is our slide as you can see we have input table and we have two output tables so in input table we have two columns first is id and second is name in id we have integer data and in name we have alphanumeric data in our output you can see we have two tables first is for the integer value and second is for the string values so in our first we want to get the output where we want only integer values are present in name in our second output we want only string values are present in our input table so in our second table we want to get only string values which are present in name column in our input so let's move to SQL server management studio where we will discuss how we can get the output so I am using SQL 2014 you may have another version of SQL server but query would be same for all the versions so I have already typed the query so it would save the time we can create the table so table has been created successfully and then I am going to insert some record on it so five rows affected so five rows has been inserted so this data which I have in this table and as I have displayed in our slide both are same there are five rows in our slide and we have in our input table we have five rows in our input so before going forward I would like to explain about the pat index of string and replace functions so let's start with pat index pat index is very similar to care index the only difference between pat index and care index we cannot use wildcard characters in care index but we can use in pat index so let's create a scenario by which we can easily understand about the pat index so select then we have to specify the pat index and then the search pattern so what value we want to search like we want to search p a k and then from where we want to search we have to specify our second parameter so we want to search from this string so from this string we want to search p a k now we can execute we can execute and we can see it's going to return four and we can count in our second parameter string d then double e then p so p comes in fourth position so it's going to return four so this is our pat index and it's very easy to understand so now let's move to servicing so servicing function is used to fetch the specific length string from any string let's create a scenario by which we can understand about the servicing function so here servicing then expression so here we have to specify the expression by which we want to fetch the string like we have so we have to specify the string so it's debug and now we can see starting position so starting position we want to and then the length how many characters we want to get from this string we want only two characters and then we can execute now we can see it start from e and it will fit these two characters in our output so we can see and it's very easy to understand about the servicing function move to replace function so in replace function select and then replace select then replace and here we have to specify the string by which we want to replace we want to replace from debug what character we want to replace i want to replace e and then from which character we want to replace by t so close the bracket and execute so now we can see in our output e has been replaced by t so these are the three functions i have explained here so now i am going to use all these functions to get the output so now i have already typed the query so let's move to our query so this is our query so i have declared a variable which is a string type and i want to use a loop and inside this loop and i want to check if the value present in this string variable is not an integer value because it's going to check between 0 to 9 if the value present in this string variable is not between 0 to 9 then it will return the location of that character value so inside this loop we can see so our inner function is pat index as you can see this is our inner function so i want to explain about this pat index so pat index is going to return the first non integer value so it will return the first non integer values location then i have used this servicing function so in servicing function it so it will return the first non integer character because pat index will return the location where first non integer value character is present if it's in first position then it will return one and then in servicing function i gonna use this pat index so it will start from one and we want to fetch only one from this and then our last function is replace function so replace function is used to replace this non integer value by blank once it would be replaced then it will again check and then again check until unless value is not only integer so let's create a function by which we use this formula so i have already created the function so this is our function i wanna execute so function has been created successfully and then so we can use this function as inline function so this is our table as i have explained so select store from this table and then we can see so execute and now we can see value is present we can use this function and it takes a parameter so in parameter i wanna use name and now i wanna execute so we have to use comma then dbo dot function name and now i am going to execute so here we can see our string value has been replaced by blank and now i wanna use so now we can get only integer values from this input string so we cannot use the store procedure to get the same output in inline query but in function we can get the inline query and now as we can see we have used pat index where this is not equals to so we can use equals to here i wanna use one more function and now name of this function is get integer value get string value and now i wanna replace this not equal to sign i wanna execute and we can get the output so function has been created successfully and we can use this function over here in our select query then put comma dbo dot then the function name and now we can use name and now we can execute and we can see it will return the output okay so there is some mistrack i did we have to alter this function because we have to replace this not equal to sign over here as well so now alter this function and now execute this query and now we can see we get the output so this is for integer values and this is for string values as i have displayed over here so one output is for integer another output for string so we cannot use store procedures in inline query but we can use functions in with inline queries so this is the difference between store procedure and function where we cannot use store procedure and we can only use functions i hope you have understand the real-time use of function over store procedure if you like this video please subscribe our channel for many more videos if you need this script it will be available on my facebook page you can copy from there thank you so much hello friends welcome to ssunitech my name is susil Singh and this is continuation of sico server interview questions and answers so today i gonna discuss one more question how we can switch any column values from bottom to top by using sico server joints so what it means let's move to another slide where we will discuss about the question this is our slide as you can see we have an input table and it contains two columns first is id and second column is name in id column we have one two three four five data in a sequence and incremented by one so here we can see in our output we have id and we have the reverse name like if id is one then go to the bottom in this table and we can see the name this is f so it would come first then our second then the second last value is e so we want to get the values in name from bottom to top so how we can achieve this biogic sico server query so let's move to sico server management studio where we will discuss how we can get the output so here we can see i have already typed the query so it would save time so i want to create the tables i'm going to execute this creative statement the table has been created successfully now i want to insert some recall on it so here six was affected and now we can check by using select start from table so here we can see we have the same metadata as i have displayed in our slide so first of all we need to copy this output and go to excel to understand the logic how we can apply logic and get the output i want to apply self join so i have to paste two times so here we can see if we have one more additional column over here which is reverse id and this id is started from the bottom it's six so it's six and then five then four three two and one let's assume this is our a table and this is our b table so when we apply inner join between these two tables on the behalf of id and reverse id and when the id and reverse id value will be matched then we want to get the name from b table so our first is id one when id value will be matched then we want to get name from b table which is f so it will return f for next time we can see id is two and when we are going to match with our id then we can see it's going to return e so it's going to return e then d then c then b and then a and we can see in in our slide here we want f ed cba and here we can get the output by using this method so let's move to sequence of management studio where we will write the query so first of all we need to create a reverse id so we have to use row number function so this is our row number and we have to specify over clause and then order by this id and we are going to get from bottom so we have to use decreasing and I am going to provide an alias name which are id so reverse id and now I want to execute and here we can see this is our id and this is our reverse id and I am going to put order by one on the behalf of id now I want to execute and here we can see id and we are getting reverse id is six it's going to start from bottom six five four three two one so now our b table has been created successfully so now put this b table in a derived table and provide an alias name is b and then we want to apply join from this over switch table column values tested here and provide an alias name is a then specify inner join and then on the behalf of id from table one and reverse id from table b so it's our reverse id when these value will be matched then we want to get id from table one and name from table b so this is our table b and now I want to execute this query so here we can see we are getting same output as I have displayed in our slide so this output and this output is same so I hope you have understand how we can get this output by using sequence of our query if you like this video please subscribe our channel for many more videos and press bell icon to get all the notifications for new videos if you need this script it will be all available on my facebook page you can come from there thank you so much for watching hello friends welcome to ss unit x my name is susil Singh and this is continuation of sico server interview questions and answers so today I'm going to discuss one more question which is related to interviews so this question is totally dependent on values based query so what it means so let's move to another slide where we will discuss about the question so this is our slide as you can see we have a input table and it contains two columns first is id and second is name in id column we can see we have repeated ids and in name column we have null values available so we want to skip those records where id is same and one record in name column is null and another record is not null so null record should be eliminated in our output as you can see in id one we have four rows and we can see we have name column and it contains 10 11 and then two null values in our output we don't have null values we have only not null values like we have 10 and 11 in our output if we have only null values in name column with reference to that id so we want to get that record in our output but record should be unique and we can see id is three and name value is null so we want to get in our output but the id is four we have repeated values id is four we have two rows and both are null so we want to get in our output only once as you can see over here so how we can achieve this output by using sico server query so let's move to sico server management studio to write the query and get the output as i am using sico 2014 you may have another version of sico server but query would be same for all the versions i have already typed the query so we can execute and we can create the table and insert record on it so nine rows affected table has been created successfully as well and we can execute and we can see this is our input table so we need to write the query so first of all we need to select only those records where value column doesn't contain any null values so we have to use where clause and then value is not null so we have to use is not null and then we have to use union all and then we need to select those records where value column contains null values so we can copy and we can paste it here and we can remove this not keyword so it will return only those records where value column contains null values and then one more thing we need to check those id's where value column doesn't contain null values and we have to eliminate in our second query so we can use and keyword id not in and then we have to use a sub query by which we can select only those records where value column doesn't contain any null value so we can copy this query because the query going to return only those records where value column doesn't contain any null values and we can select only id from here and now we can execute and we can compare or before going forward I would like to use order by keyword and then we can execute the query and we can see here it's going to return seven rows and we can see four is coming two times and value column contains null values but in our output it's coming only once so we have to use distinct keyword in our second query so we have to use distinct and now we can execute and we can compare our output so we can see it's going to return six rows and in our slide we can see we have six rows so both are same so I hope you have understand how we can get the output by using sequence of a query if you like this video please subscribe our channel for many more videos and press bell icon to get all the notifications for new videos if you need this script it will be available on my Facebook page you can copy from there thank you so much for watching hello friends welcome to SS Unitech my name is Sushil Singh and this is continuation of SQL Server interview questions and answers so today I gonna discuss about Pivot operator and dynamic Pivot operator so first of all we need to discuss about the Pivot so what is Pivot? Pivot is a SQL Server operator that can be used to turn unique values from one columns to multiple columns in our output basically it's a rotation of table so what it means let's move to another slide where we will understand so this is our slide as you can see we have an input table and it contains five columns first is ID second is customer ID then mobile and then date and then duration in our output we can see we have only four columns first is customer ID and then the mobile number and we can see it's available in our input but next two columns are coming from date we can see we can turn unique values from date column in our output how we can achieve this output so let's move to SQL Server management studio to write the query and get the output as I am using SQL 2014 you may have another version of SQL Server but query would be same for all the versions first of all we need to create the table so I wanna create the table table has been created successfully and then I wanna execute insert a statement and insert some record on it so three rows affected and we can select and we can check so data is available as I have displayed in our slide first of all we need to write the Pivot operator so for using Pivot operator we have to specify Pivot and then open bracket and inside the bracket we have to use that column by which we want to aggregate the values so we want to sum of these durations then for which column we want to turn the unique values we have to specify over here so called it is the work column and then we have to use in operator then again open the bracket and inside the bracket we have to specify the values so here we can see we have two unique values first is 24, 3, 18 and second 25, 3, 18 so we have to specify over here so 2018, 3, 24 and then 2018, 3, 25 we have to specify in our selected statement as well we want to get four columns first is customer ID and then the mobile number so we can see mobile number customer ID and then two columns and we have 24 so we have to specify 24 and now we can execute we are getting some error so we have to specify a name for this Pivot operator as I am using PVT now we can execute and we can see so here we can see it's not going to return as we want in our slide so in our slide we can see we have only one row by which we are getting exact same output but here we are getting three rows so why it's not going to return as same output as we want because in our table we have an ID column and this ID column is not used in our output if we don't want to use ID column in our output so we have to remove this column from our selected statement so we have to select only those values which are going to use in our Pivot operator so we have to use only four columns so first is customer ID then the mobile and then the call date and then the duration so we have to use duration and then we have to set in a derived table and we have to put an alias name for this derived table and we can execute this query and now we can compare between our slide and in our output so here we are getting same output as we want so I hope you have understand how we can use Pivot operator if we have a scenario by which date column values is going to increase on daily basis then how we can write a query by which there is no need to put changes on daily basis so let's move to another slide where we will discuss so here we can see we have input table and it contains six columns first is customer ID then the customer name then the mobile number call date start time then the duration of that call and in our output we can see customer ID name and mobile number is also present in our input table but we want to turn the unique values from call date column in our input to our output in our output we have 23 18 and 21 3 18 22 3 18 and in input table we can see we have 20 21 and 22 these three unique values in call date column so how we can write a dynamic period by which we can achieve this output so let's move to SQL Server Management Studio where we will discuss how we can write the query now I want to execute this greatest statement table has been created successfully then I want to insert some record on it so we can execute this query and here six rows vector so six rows have been inserted in our table so we can execute and we can compare between slide so these metadata are same so first of all we need to put unique values from this table to a temporary table so we can choose distinct keyword and then call date into as stamp so unique values will be inserted in our table so now we have to declare a variable declare at the rate this is the column name so we have to use at the rate call and this is vector max here we can see we have to use a loop by which we can create the column dynamically so we have to use a while while exist so I am going to check the values whether this these values are present in our temporary table then this loop will be executed and here begin and so here we need to select the first row so we have to use top operator top one and then from this temporary table and we need to assign in our column variable so column variable is equals to at the rate column then we need to concatenate so first of all we have to specify our bracket then we have to cast and then called it is our column name as where care 20 and then again we need to close and then put comma so it's going to set the values in our column variable and then we need to read top one from temporary so it will read our first row and now I want to select and we can check so table has been created already so we need to so here it's going to return null value because we have to specify blank at the starting but at this time we don't have any value in this temporary table so we have to drop and recreate and then we need to execute and here we can execute and we can see it's going to return the value but here we can see we have a extra comma so we have to remove that comma so we need to select values from left of this variable call and then we need to check the length and we need to subtract one and it will set the value so we can execute and we can see comma has been eliminated so we have to select as well so now we can execute and we can check so here we can see our column is created successfully so next we need to write a dynamics equal by which we can put the pure operator we need to declare one more variable we can call as SQL and then work a max first of all we need to in slice as blank so first we know how we can write pure operator so we can write select from then our table name so what is our table name dynamic your table is our table name so we have to specify over here so this is our table and in our selected statement we need to select only three columns as we have displayed in our slide first is customer ID so this is customer ID then the name and then the mobile number so this these three columns are going to select over here and then we need to concatenate so we have to use plus and then add the red SQL so this is our dynamically created columns as we have discussed we have to select only those rows which are going to use in our pure operator so we need to eliminate those rows which are much part of your operator so we have to use all these three columns and then the call date and then we have one more column which is duration so duration so these columns are required so we have to use only these columns in our selected statement so here we can see we have done almost so we need to use pivot and then brackets and here we need to put some of duration and then we have to use for and we have to specify call date and then in we have to use bracket at inside the bracket we have to use our column so this is our columns and here we need to use an alias name so I am going to put these values in our dynamics equal so this is and then we need to put plus sign over here and over here put any space and then comma then plus then again plus plus here we need to set and then again plus sign and then and I am going to set these values in our SQL variable select and we can check what values are present in our SQL variable so here we can see we have this statement we can copy we can paste we are not getting the column names over here we have to use at the rate column now I am going to execute and we can copy paste it over here and now I am going to execute as we can see we are going to get the output so first of all we have to use execute this dynamic SQL and then we can see now we can see we are going to get the output I hope you have understood how we can get the output by doing dynamic rate operator thank you so much for watching this video if you like this video please subscribe our channel for many more videos and press bell icon to get all the notifications for new videos if you need these scripts it will be available on my Facebook page you can copy from there thank you so much for watching hello friends welcome to SS Unity my name is Susheel Singh and this is continuation of SQL Server interview questions and answers so today we are going to discuss about what are the real-time use of un-pivot operator when interviewer asks such type of question to us then they want to know have we used these objects in our real-time scenario or not so here I have created a scenario if interviewer asks this question to you then you can explain the scenario to interviewer so let's move to another slide where I have created a scenario so here we can see we have two input tables first table contains the information of location and second table contains the information of invoice so in our first table we can see we have column one which is location and this is our from location and rest of the columns contains the information of two locations so here we can see when we want to deliver any item from location Mumbai to location again Mumbai then it doesn't take any time so if any customer purchase any item on the same day it would be delivered if item would be delivered from Mumbai to Bangalore then we can see it takes three days in our second table it contains the information of invoice as you can see we have invoice number customer ID date from location to location and in our output we can see invoice number customer date from location and to location is same but we want to calculate the expected so expected delivery date would be calculated from location table but here we cannot apply join between these two tables because location table doesn't contains the data in normalized form so how we can get the normalized data so we have to rotate our columns to our rows so it would be very helpful by using join we can get the output so how we can do the same so let's move to as a slide so this is our slide by using unpivot operator we can rotate the column values to our row values as we can see we have from location and then to location to location comes from columns like Mumbai is our from location then to location is Mumbai and then second column is Bangalore then Bangalore then third Delhi so Delhi Lucknow then Kolkata and value is 0 3 as we can see in our input table once we will get the data from this format then we can simply apply join between invoice table and this unpivot output table then we can simply calculate expected delivery date so how we can rotate this table to normalized form so let's move to SQL Server Management Studio as I am using SQL 2014 you may have another version of SQL Server but query would be saved for all the virtuous so here I have already typed the query to create the table so first I gonna create location information table so this is location information table so table created successfully and then I want to insert some record three rows affected then I gonna create invoice information table so execute this statement table has been created successfully and then we gonna insert record on it so we can see four rows affected as we can see in our slide so now we have location information table and we want to rotate this location information table so how we can rotate select star from our table as we can see it contains it contains the information as we have explained in our slide so we have to use unpivot as we are using pivot operator in our previous slide if yo