 Hello, welcome to SSUnitech, so soon decide and today we are going to see two more SQL interview questions and answers So recently one of my subscriber has attended interview. So these two questions was asked there So in the first question that we can see we have two table like the table one and table two and Both are having the same Column ID and location in the table one we have total four rows and in table two We have total five rows but in the output. We just want to get all the five rows But here if we are having any blank for that particular ID, for example, we can see one zero two in the Table one then it should be going to get the Location from the table to which is the Mumbai. So that's why you can see the Mumbai here If both the tables are having the locations Then we just want to concatenate with the comma as you can see for one zero five We have the Bangalore and one zero five. We have the Delhi so in the output We can see Bangalore comma Delhi. So how we can write the query for getting this output So let me quickly go inside the SQL server and we'll try to implement this question so here as I have declared two tables table one and table two and Inserting the data into these two tables. So let me try to execute this to check The values so as we can see first table is having total four rows and second is five Now how we can achieve the output that we have seen in the word document So for that we are required to use the full outer join So what full outer join will do it will be going to get all the values from the table a and from the table B and Let me try to join on the basis of ID. So a dot ID and B dot ID Now, let me try to execute it. So Here we could see we have the location and Here we have the location But the next thing we just want to concatenate Like the location from table one and the location from table two But if we are having any null value, then it will be going to return as null and if we are having any blank value then it will be going to concatenate as blank comma Mumbai But we don't want to see the output like that. So the first thing that We are required to use the ID. So first let me use the is null function So what is null will do it will be going to get the ID from the table one If ID is missing in the table one, then we want ID from table two. So first thing We have done. Let me try to execute as you could see here. We have the proper ID. That looks okay Next let me try to check if your table one location is blank or null Then we want to get the location from the table two. So for that we are required to use the null function So what null function will do it will be going to check the location of the table one If this location value is blank, then it will return null. So let me try to execute and we will show you So here as we could see it is having Bangalore then null because this location value is null. So that looks great Now if this value is null, then simply we can Get the location from the table two. So we can simply use the is null function And here let me try to get the location From table two Now let me try to execute it again So here we can see we have Bangalore, Mumbai, Hyderabad, Chennai, Kochi. So everything is looking perfect But here as we could see we have the locations in the second table as well If table one and table two both are having the locations, then we just want to Concatenate in case of the 105. We just want to concatenate Bangalore with the deli So we have to write the case statement. So case when and here we have to check if your a.location value is Either not blank or not null. So what we have to do? Let me simply copy this Relief condition and here Let me try to check if this value is not null. So in this case it will return the first row Then the third row and fourth row the second row and fourth row will be skipped Now the next thing again we have to check if your b.location value is Not null or blank. So it will return the first row. So here let me use the AND condition And this same query we can use And instead of a it will be b.loc So it will be returning only the first row. So what we have to do? We have to use then Here we should be going to return b.location And it will be going to concatenate with comma If it's not then we will be going to return as blank Now let me try to execute this query. As we could see it is returning over here. So Let me try to concatenate this value with the actual column. Let me try to execute it So as it is going to return the output as we seen in the word document Let me quickly see the second question. So here in the second question We have this table which is the employee table and it is containing employee id and email id And in the output we just want to see the employee id And the email id But if we are having multiple rows then those Value of the email we'd be going to get as comma separated as we can see test one comma test two comma test three And then test four then we have the test five six seven and eight So it will be going to return the values like this. So let me quickly go inside the SQL server here. I have declared this table which is the employee table and let me try to select and check the value so Here we should be going to see all these rows now how we can achieve the output as we have seen So this is very straightforward. We can use the string underscore aggregate function And it will be going to ask the first parameter your column which we want to rotate And next what will be the separator we can specify here Now as here we are going to use the aggregate function So we are required to use the group by on the employee id. Let me try to execute it So here as we could see we are able to see the data as We have seen in the word document. So, thank you so much for watching this video. See you in the next video