 Hello friends, welcome to SSUnitech. My name is Susheel Singh and this is continuation of SQL Server interview questions and answers. So today I gonna discuss one more question which is related to interviews. So this question is totally depend on values based query. So what it mean? 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 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 wanna 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 SQL Server query. 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. 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 given. So it will return only those records where value column contains null values and then one more thing we need to check those IDs 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 subquery by which we can select only those records where value column doesn't contain any null value. So we can copy this query because this 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 before going forward I would like to use order by keyword and then we can execute this 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 SQL Server 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.