Microsoft SQL server select statements on multiple tables?

So, Basically what your code is doing is 2 different queries. The first getting all the employees names, and the second one getting the dates of the assignments.

What you'll want to do here is take advantage of the relationship between the tables using a JOIN. That is basically saying "Give me all employees and all of HIS/HERS assignments". So, for each assignment that the employee has, it will bring a row in the result with his name and the assignment info.

To get the difference between days you use DATEDIFF passing 3 parameters, the timespan in which to calculate the difference, the first and the second date. It will then Subtract the first one from the second one and give you the result in the selected timespan.

And finnaly the sorting: Just add 'ORDER BY' followed by each column that you want to use for ordering and then specify if you want it ascending (ASC) or descending (DESC).

You can check how I would answer the if that question was proposed to me in a coding challenge.

    SELECT 
       CONCAT(E.First_Name,' ',  E.Last_Name) FullName,
       DATEDIFF(DAY,Scheduled_End_Date,getdate()) DaysTillScheduledDate,
       DATEDIFF(DAY,Host_Country_Arrival_Date,Host_Country_Departure_Date) DaysTillScheduledDate
    FROM Employee As E                      --Is nice to add aliases
         Inner Join 
         Assignment As A
          on E.Employee_ID = A.Employee_ID  -- Read a little bit about joins, there are a lot of material availabel an its going to be really necessary moving forward with SQL
    order by Host_Country_Arrival_Date DESC -- Just put the field that you want to order by here, desc indicates that it should be descending

You should use a JOIN to link the tables together on Employee_ID:

SELECT 
   First_Name,
   Last_Name,
   Host_Country_Arrival_Date,
   Host_Country_Departure_Date
FROM Employee 
JOIN Assignment ON Assignment.Employee_ID = Employee.Employee_ID;

What this is saying basically is that for each employee, go out to the assignments table and add the assignments for that employee. If there are multiple assignments, the employee columns will be repeated on each row with the assignment columns for the assignment.

You need to look for the join and group by. Please find this link for reference tutorial

For now you may try this...

SELECT 
   CONCAT(Emp.First_Name,' ',  Emp.Last_Name) FullName,
   DATEDIFF(DAY,Scheduled_End_Date,getdate()) DaysTillScheduledDate,
   DATEDIFF(DAY,Host_Country_Arrival_Date,Host_Country_Departure_Date) DaysTillScheduledDate
FROM Employee As Emp Inner Join Assignment As Assign on Emp.Employee_ID = Assign.Employee_ID  
order by Host_Country_Arrival_Date DESC