AdventureWorks Database: List contacts who are Purchasing Manager

SQL Query - AdventureWorks: Exercise-20 with Solution

20. From the following tables write a query in SQL to make a list of contacts who are designated as 'Purchasing Manager'. Return BusinessEntityID, LastName, and FirstName columns. Sort the result set in ascending order of LastName, and FirstName.

Sample table: Person.BusinessEntityContact

Click to view Full table

Sample table: Person.ContactType

Click to view Full table

Sample table: Person.Person

Click to view Full table

Sample Solution:

SELECT pp.BusinessEntityID, LastName, FirstName
    FROM Person.BusinessEntityContact AS pb 
        INNER JOIN Person.ContactType AS pc
            ON pc.ContactTypeID = pb.ContactTypeID
        INNER JOIN Person.Person AS pp
            ON pp.BusinessEntityID = pb.PersonID
    WHERE pc.Name = 'Purchasing Manager'
    ORDER BY LastName, FirstName;

Sample Output:

businessentityid|lastname      |firstname  |
----------------+--------------+-----------+
            1149|Alexander     |Mary       |
             363|Arakawa       |Hannah     |
             365|Arbelaez      |Kyley      |
             377|Ault          |John       |
             379|Avalos        |Robert     |
             389|Bailey        |James      |
             391|Baldwin       |Douglas    |
             399|Banks         |Darrell    |
             401|Barbariol     |Angela     |
             403|Barber        |David      |
             409|Barlow        |Brenda     |
             411|Barnhill      |Josh       |
             413|Barr          |Adam       |
             423|Bauer         |Ciro       |
             425|Beanston      |Glenna     |
             427|Beasley       |Shaun      |
             447|Ben-Sachar    |Ido        |
...			 	

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Contacts designated as a manager.
Next: Retrieve the salesperson for each PostalCode.