w3resource

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to create a foreign key in SQL Server?

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);

Database: SQL Server

Ref : https://bit.ly/3sgS152