sqlsql-serveradventureworks

AdventureWorks queries


I am trying to answer this question on AdventureWorks:

Print the information about all the Sales.Person and their sales quota. For every Sales person you should provide their FirstName, LastName, HireDate, SickLeaveHours and Region where they work.

This is what I have been able to do:

SELECT 
    Person.FirstName, Person.LastName, 
    Employee.HireDate, Employee.SickLeaveHours, 
    SalesPerson.SalesQuota, SalesTerritory.CountryRegionCode
FROM
    Person.Person, HumanResources.Employee, 
    Sales.SalesPerson, Sales.SalesTerritory
WHERE
    Person.BusinessEntityID = Employee.BusinessEntityID;

but it doesn't completely answer the question and I am struggling to connect tables together.


Solution

  • Use proper ANSI INNER JOINs to connect the tables, and preferably also use table aliases to make your query more approachable and more readable:

    SELECT 
        p.FirstName, p.LastName, 
        e.HireDate, e.SickLeaveHours, 
        sp.SalesQuota, st.CountryRegionCode
    FROM
        Person.Person p
    INNER JOIN 
        HumanResources.Employee e ON e.BusinessEntityID = p.BusinessEntityID
    INNER JOIN
        Sales.SalesPerson sp ON sp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN
        Sales.SalesTerritory st ON st.TerritoryID = sp.TerritoryID