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
Sample table: Person.ContactType
Sample table: Person.Person
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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join