 Hello, welcome to SSUnited Society site and this is continuation of SQL Server interview questions and answers. So today we are going to discuss top 6 tricky SQL carries interview questions and answers. So these are the very common SQL interview questions which is generally asked in interviews. So let's start with the questions. So as we can see this is our first question. How to replace the multiple comma from the below input by single comma? As we can see we have an input string that would have ABC then 3 times comma then DDDD then multiple commas like this. So we just want to replace all these comma by a single comma. As we can see in the output we have only a single comma instead of multiple commas. So how we can write the query for getting this output? So let's move to SQL Server management studio where we will write the query for getting this output. So as I am using SQL 2017 you may have another version of SQL Server but this query will work for all the versions. So as we can see here I have declared a variable that is input string and it has the same string as we seen in the slide. So we can execute and we can check. So as we can see this is our input string. So what would be the logic to replace these multiple commas by a single comma? So first of all here we have to use the multiple replace functions. So first I just want to replace and this time I want to replace a single comma and here we can replace by dot or anything and comma. Now we can check the output for this replace string. So as we can see we are getting dot comma then dot comma dot comma where we have the comma. Then we just want to replace comma dot from this input string. So we can use one more replace function and here I want to replace comma dot by blank. Now we can execute and we can check the output. So here as we can see we are having dot comma dot comma dot comma dot comma and dot comma. So now what we need to do here we just want to replace dot comma by comma. So we can use one more replace function. So this is our third replace function and then we can replace this dot comma by a single comma. Now we can close the bracket and we can execute this query. So here as we can see we are getting the only single comma instead of multiple. So we can copy and paste it here and now we can check only single comma instead of multiple commas. So this query will help us to retrieve single comma instead of multiple commas. So let's move to next question. What would be the output for below query? So as we can see here we have a source table and we have destination table. In source table we have only one column and in destination again we have only one column. So in source we are having one and one. Then in destination we don't have anything right now. So here we have used a merge statement. Then if value is matched then we just want to update the output. If value is not matched then we just want to insert record on the destination table. So what would be the output in the destination table after this merge statement? So as we can see here we have duplicate values one and one. So in the destination what would be the output? It may be in a single one or it will be double one. So let's move to sicko show management studio to check the output. So this is the query as we seen in the guide. Now we can execute this query and we can check the output. So as we can see this is our source as we can see here next as we can see this is our destination. So in destination we are having multiple worlds. Like if value is not available in the destination it doesn't matter in source we have duplicate or not all values will be inserted in a batch to the destination. Now move to next question. Here we need to write a query to get the occurrence of S character in the below string. So as we have an input string that is SSUnited. So here we just want to get the occurrence of S. So here in this string we have S2 times so output is 2. So how we can write the query for getting this output? So let's move to sicko show management studio to write the query. So this is the third question. Here I have declared a variable that is at the rate name. And we have initialized the value as SSUnited. So how we can get the occurrence of characters? So for that we can use the replace function. So first I just want to replace that character which occurrence we want. So here as we can see we just want to get the occurrence of S. Now we can execute and in this string we shouldn't have S like this. Now we can calculate the length of this so we can use the length function and then we can close the bracket and execute the query. So this is it. So as we can see this should have the 10 characters. Now how we can get? So we can use the length of this SSUnited that is at the rate name variable and we can subtract this now we can execute and we can check the output that is 2. In the slide as we can see output is 2. So by using this we can get the occurrence of any characters. Now move to next question. So write a query to get the attendance count. So as we can see here we have input table that would have 2 columns. First is ID that is employee ID then date present. So here in date present we have comma separated values like first, second and third. Then 2, 4, 5, 6 then 1, 2, 5, 4, 7 and 9. So in the output we can see we are having 2 columns first is employee ID that is fine. Next total number of days that employee was present. So here as we can see employee ID 1 he was present for first, second and third. So output should be 3. Next for ID 2 he is present second, fourth, fifth and sixth. So output should be 4. How we can write the query for getting this output? So let's move to Seco Server Management Studio where we will write the query for getting this output. Here I have declared a table that is attendance. Now we can select and we can check the data which I have inserted in this attendance table. Now let me execute this query. So as we can see we are having the same metadata as we seen in the slide. Next how we can get the attendance count? So we just want to get employee ID that is EMP. Next we want to get the count. So for that we can use the same as we seen in the occurrence. So here we just want to get the occurrence of comma. We can get the length of this date present column. Next we need to use the subtract length of then replace and this time we want to replace from this date present column and what we want to replace? We want to replace comma by nothing. Now we can execute and we can check the count. So here we are getting 2, 3, 5 and 4 but as we can see we are having comma which is less than 1 attendance count. So here we need to add 1 for getting the attendance count. Now let me execute the query and here we are getting 3, 4 and 6. Now we can compare with the slides output. So here 3, 4 and 6. Move to next question. So here how to write an update query to swap the column values like here we can see we have ID column then name then in the output we are having ID and name but as we can see in the output we are having ID, AA, BB, CC but in the input we have 1, 2, 3. So we just want to swap the column values. So before going to write the update statement first you need to check the data type of ID column. Mostly interviewer ask this question and we directly write the update statement before checking the data type of this ID. Query will be failed if the data type of ID is integer. So first we need to check that one. Next we need to write the update statement. Move to Sikoso amendment studio to write the update statement. So here as we can see I have declared a table that is trivial and we have inserted data on this table. Now I can execute and I can check the output. So here as we can see we are having AA, BB and CC but as we can see ID column that is where care. So we are happy we can write the update statement and that update statement will work. So we can write update then set then add the rate trivial and this time ID should be equals to name and name should be equals to ID. Now I can write select a stick from our table that is add the rate trivial. Now let me execute this query and check the output. So here as we can see data is swapped. Move to last question. So here write a query to generate the sequence number from 1 to 100 by using CTE. So how we can write a CTE to generate a sequence number from 1 to 100. So let's move to Sikoso amendment studio and here I just want to write the query. So first I have declared a variable that is add rate I this is the integer and here I have initialized the value that is 100. Now how we can write the CTE to generate the sequence number up to 100 which is declared in this variable. So we can write the width then CTE as and here inside this we can use 1 as ID. Now we have to use a recursive CTE to generate the sequence number. So we can use union on then we just want to use a select from the CTE. Next I just want to increase the value of this ID by 1 and in the where clause we can put this ID value should be less than at the rate I which is our variable or we can directly specify 100 over here. Now we can write a select statement from the CTE. So we can execute this query and we can check the output. So as we can see it is going to generate a sequence number from 1 to 100 last row value that is 100. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos.