 Hello, welcome to SSUnited Succeeded site and this is continuation of SQL Server interview questions and answers. So today we are going to discuss two more questions which was asked in Optum. So first is write the query to fetch all the employees who doesn't have department names in the department master table by using joins. So here as we can see we have two input tables first is employee and second is department. In employee table we have two columns, first is employee ID then department ID. In the department master table we have department ID and department name. So here we want to fetch those employees where department ID is not available in the department master table but here we don't need to use the in operator or not exist. So we have to use join. So how we can write the query for getting this output 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 first of all I would like to execute the create table statement. So tables have been created successfully first is employee table and second is department table. So now let me execute these two statements to insert data on the tables. So as we can see four rows affected and four rows affected. So data have been loaded successfully into the tables. Next we just need to check the data. So we can execute select a stick from table. So as we can see here we are having the same metadata as we seen in the slide. So how we can put join between these two tables for getting the output. So first of all we want to get data from the employee table. So we can write select a stick from. And here we can put the employee table. Then we need to use the left join. So and then left join with department table. Now I need to put alias for employee as E and for department as D on employee dot department ID should be equals to department dot department ID. Let me execute this query and check the output. So here as we can see we are getting the data but here in the department table we are getting the null values because those departments are not available in the department table. So we want to get only those employees. So here we can put where clause so in where clause D dot department ID is null. Now let me execute and we can check the output. So here we are getting only one row that is four. So in the selected statement we want only two columns as we seen in the slide. So E dot employee ID then D dot department name. Now let me execute and check the output. So as we can see we are getting four and department name is null. So we can check we are getting the same meta data. Next question is here as we can see we have two input tables. First is student, second is employee contact. So in employee contact we are having the four numbers like mobile number and landline number. In student table we are having four columns student ID name, data birth and gender. As we can see in the output we want all the data from the student table and we want the contact detail from the contact table. So here we need to write the query to select all the data from the student table along with the mobile number for each student. Contact numbers are mobile number or landline numbers. So if the type is M then that is mobile number. If type is L then that is landline number. In case if any student doesn't have mobile number then select their landline number. So as we can see for the student one so he has only mobile number. So we need to display in the output. Then for the student ID two he doesn't have mobile number. So in this case we need to display the landline number. Next for ID three she has mobile number as well as landline number. So we need to display the mobile number only. Next for ID four so she also has mobile number and landline number. So we need to display the mobile number. So as we can see in the output so how we can write the query for getting this output. So let's move to SQL Server Management Studio. And here first of all I would like to execute these two create statement to creating the tables first for the student and second for the contact detail. So command completed successfully. So tables have been created. Now I just want to insert data on these two tables. So let me execute the insert statement. So as we can see four rows affected and six rows affected. So four rows have been moved into student table and six rows into contact detail table. Now we can write the selected statement and we can check the data. So we can write select as stick from first student table and second contact detail table. So select a stick from contact detail table. Now let me execute and check the output. So as we can see here we are having the same metadata as we seen in the slide. So how we can implement the logic. So now let me copy the data of contact detail and go to on Excel to understand about the logic. So here paste that data and it should be number. Here as we can see if we will generate a sequence number if we have mobile number then that is one. If we have line number only then one if we have mobile number and line number. So first for the mobile number and second for the line number then first for the mobile number and second for the line number we will apply filter where rw value equals to one then we will get only that data which need to be displayed in the output. As we can see for the id1 we are having mobile number only. So mobile number in the output for id2 we have only line number so that is going to display in the output for id3 and 4 these two has mobile number as well as line number. So we need to display only mobile number. So if we will generate a sequence number like this then we can simply apply filter and we can get the output. So how we can write the query. So here first of all we need to create a sequence number for this contact detail table. So we can use the row number then we can use the over clause then we have to use the partition by and partition by on student id then we have to use the order by clause and order by should be on type in decreasing order. Now we can use as rw. Now let me execute this query and check the output. So as we can see we are getting 11 for mobile number 1 and line number 2. Mobile number 1 line number 2. If we will apply filter on this then I hope we will get the output. So how we can write the query and we can get the output. So here let me call this as a then use the inner join or left join and put this into a derived table and call that table as b then on a.student id should be equals to b.student id and b.rw value should be equals to 1. As we seen in the excel now let me execute this query and check the output. So I guess we are getting the output as we were expected. So here we want all the columns from the employee table and we just want the phone number from the contact detail table. So now let me execute the query and we can compare the output. So we are getting the data as we were expected. So I hope you have understood how to write these two queries for getting the outputs. In the next video we will discuss few more questions which was asked in Optum. 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. If you need this script it will be available on the description of this video so you can copy from there. Thank you so much for watching.