Question

1. Link the following two tables: HumanResources.Employee and Sales.SalesPerson then display Employee PK, job title, Date of birth, Gender, Sales quotas, Commission percent and bonus. Use an outer join to display all the employee whether they are in sales or not. Make sure to sort by bonus desc. Explain why some of the field from the Sales.SalesPerson table are null.

Answer 1

1).ANSWER :

GIVENTHAT :

Here we have used left outer join to join the two tables with the help of a common key which is not specified but I am assuming employee_pk and sales_id to be same.
If it is different then you can changed accordingly or you can comment I will change and edit the code for you.

The table in joint using left join which is same as left outer join and required columns are shown and it is sorted by bonus in descending order.

here we have used left outer join because in the question it is ask to display all the employee whether they are in sales or not so here we have to use left outer join.

Some of the fields from sales.salesperson table are null because no matching row in the   sales.salesperson table is found in humanresources.employee table so some fields from the sales.salesperson table are null

The SQL code

SELECT Employee PK,
       job title,
       Date of birth,
       Gender,
       Sales quotas,
       Commission percent,
       bonus,
FROM   HumanResources.Employee h
LEFT  JOIN Sales.SalesPerson s
ON h.employee_PK=s.sales_ID
ORDER BY bonus DESC ;