select
emp.BusinessEntityId as EmployeeId,
prs.FirstName,
prs.LastName,
emp.BirthDate,
emp.JobTitle,
dpt.Name as Department
from HumanResources.Employee as emp
join Person.Person as prs on emp.BusinessEntityID = prs.BusinessEntityID
join HumanResources.EmployeeDepartmentHistory as dptHst on emp.BusinessEntityID = dptHst.BusinessEntityID
join HumanResources.Department as dpt on dptHst.DepartmentID = dpt.DepartmentID