In: Computer Science
SQL
9. 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.
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 ;
You can comment if you have any doubt.
Upvote if you got your answer.
Thanks