 Hey guys, welcome to SSUNitech Social Decide and this is part 66 of SQL Server interview questions and answers. So here we are having two more SQL queries. So go to on the next slide and we will see about the question. So this is our first question. As you can see we are having two tables. The first table is T1 and second is T2. And T1 table contains only one column and the values are 1, 2 and 3. And table 2 contains only one column and it has the value a, b and c. So in the output we want to see 1, a, 2, b, b, 3, c, c, c. So basically the values from the table 2 should be repeated as much the numbers in the table 1. As you can see 1, so a is coming one time only and second is 2. So b is coming two times. Then 3, then c is coming three times. So how we can write the query to getting this output. So go to on the SQL server and here basically as you can see we have declared a table like address T1 and address T2. And the first table will be having the values 1, 2, 3 and second will be going to have values like a, b and c. Let me try to execute this. So here as we can see we are having id 1, 2, 3 and id a, b, c in the second table. So first of all night we need to have a common column between these two tables. So by using that column we can put the join. So for that we can add one more column and we can create a sequence number. For that we could use the row number and with the over clause and here we can have the order by on the id column. Now let me try to execute this. Let me try to execute with this. So here we should see the value like 1, 2, 3. So this will be the common column from the table 1 and now go to on the table 2. So in the table 2 we need to again write this row number. So it will also generate a sequence number from the table 2. So now we are having the common column between these two tables. So by using that column we could put the join between these. So we can call this as rw and here we can call this as rw. So we are having two tables. So either we can use these two tables inside the drive table or we could use this inside the city. So I am going to use this inside the drive table like this. Let me call this as a and after that we can put the inner join between these two tables and this will be the table 2. So this is as b and here let me have the select as stick from. Here we could have the on and after that a.rw should be equals to b.rw. So everything looks good. The only thing that we need to take care like we need to get the id from the table a. So we could have the id a.id comma b.id. But we want to repeat as much time the id has. So we can use the replicate function over here and in the replicate we have this b.id put comma how many number of time so a.id. We can put it close the bracket. Now let me try to execute this query. So here we can see we are having 1, a, 2, b, b, 3, c, c, c. But we are having two different columns. So we need to do the concatenate between these two columns. For that we can put plus sign but as we can see this is the integer column. So we need to convert this as wire care. So as wire care and this will be 10. Now let me try to execute this. Now it is having 1, a, 2, b, b, 3, c, c, c. So this is the first question. Go to the second question. So in the second question like we can see we are having a single table and it contains two columns. First is the employee id and second is the branch. And our question is like the employees who have the same branch are the colleague of each other. You need to write a query that can give you the colleagues of the employee id x and the list of colleagues should not include the user x himself. So for example as here we can see if we are going to pass the employee id as 4. So the result should be 1 and 5. Why? Because here we can see for the daily we are having 1 and for the 4 that is daily and for 5 again it is daily. And we are going to pass 4. So 1, 4 and 5. So these three employees are colleague and we are going to pass the 4. So 4 should not be going to appear in the output. And here we need to notice one more thing like you have to do only using one query. No nested queries or subqueries are allowed. So you need to take care by using a single query how we can achieve that. So without using the query, without using the write table we need to achieve this. So go to on the SQL server and we will see how we can achieve that. So for that here I have already declared this table and we are going to insert the values like India, US, US, India, India, Singapore and Singapore. Let me try to execute this. So in this table as we can see we are having all these values. So first of all like we need to declare a variable. So on that variable we will be going to pass the employee ID. So this is the employee ID and we could have the integer. And we could pass the value as 1. So in this case like India 1, so our output should be 4 and 5. So how we can achieve that? So for that we need to use the self-joining. So select then from ad-rate table and let me call this as a table. So this will be the left table. Now we need to use the left join with the ad-rate table and let me call this as b table. We can have on condition. So on a dot branch should be equals to b dot branch. And in the where condition we can have like a dot employee ID that should not be equals to b dot employee ID. And here we could do the filter. So a dot employee ID should be equals to ad-rate employee ID. And here let me select everything first time which is going to reflect an error because we haven't used equals to. Now let me try to execute this query. So as we can see in the below of this query, so we are getting the employee ID with the branch. So we can get everything from the a table only. So we can have the a dot as stick. Now let me try to execute this query. So here as you could see, so we are having this India and India. Now let me try to pass 6. So this time we should get only 7. So here let me pass 6 and so here we are getting 6. This is because in the a stick we are going to have from the a table. We should get this from the b table only because here if you are going to pass this from a table. So in the where condition we are going to filter for the employee ID. So it should be going to return the same that we are going to pass. Now let me try to execute this. So now this time we are going to have 7. So basically in this query what we are going to do first like we are going to do the left join between these two same tables along with the branch. And after that in the where condition we are going to check if the employee ID from the table a should not be equals to employee ID from the table b. And second condition we need to check the variable that we are going to set the value for the employee ID. So it is going to return the output and here we are not going to use any type of subquery or the CTE or any nested query. So this is only a single selected statement by using that we are going to have that output. So thank you so much for watching this video. If you need this script it will be available on the description of this video. So you can directly go and copy this and do the practice on your own. See you in the next video.