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
businessentityid|personid|contacttypeid|rowguid |modifieddate |
----------------+--------+-------------+------------------------------------+-----------------------+
292| 291| 11|7d4d2dbc-4a44-48f5-911d-a63abafd5120|2017-12-13 13:21:02.243|
294| 293| 11|3ea25b65-9579-4260-977d-d6f00d7d20ee|2017-12-13 13:21:02.320|
296| 295| 11|dadac1ff-3351-4827-9ae0-95004885c193|2017-12-13 13:21:02.383|
298| 297| 11|b924f26f-6446-45d1-a92b-6f418374f075|2017-12-13 13:21:02.447|
300| 299| 11|5ba4e7be-8d29-46a2-b68d-67b1615b124a|2017-12-13 13:21:02.510|
302| 301| 11|6cc8f248-8d96-4afd-adcc-61d93e8de3b1|2017-12-13 13:21:02.570|
304| 303| 11|33b4da81-4448-481f-bf7c-357ea4d23f21|2017-12-13 13:21:02.633|
306| 305| 11|70d35526-7c2f-470f-98cb-f9299a754f16|2017-12-13 13:21:02.697|
308| 307| 14|da33b75d-32fb-432d-a275-9e9d32e78f3e|2017-12-13 13:21:02.760|
310| 309| 14|c2dee145-a902-477e-ab21-29659ac3e97e|2017-12-13 13:21:02.820|
312| 311| 14|4020deae-56f6-47df-96c1-a01f3de2305c|2017-12-13 13:21:02.883|
314| 313| 14|9ef858de-eaba-4a53-9b28-fc699d789957|2017-12-13 13:21:02.930|
-- more --
Sample table: Person.ContactType
contacttypeid|name |modifieddate |
-------------+-------------------------------+-----------------------+
1|Accounting Manager |2008-04-30 00:00:00.000|
2|Assistant Sales Agent |2008-04-30 00:00:00.000|
3|Assistant Sales Representative |2008-04-30 00:00:00.000|
4|Coordinator Foreign Markets |2008-04-30 00:00:00.000|
5|Export Administrator |2008-04-30 00:00:00.000|
6|International Marketing Manager|2008-04-30 00:00:00.000|
7|Marketing Assistant |2008-04-30 00:00:00.000|
8|Marketing Manager |2008-04-30 00:00:00.000|
9|Marketing Representative |2008-04-30 00:00:00.000|
10|Order Administrator |2008-04-30 00:00:00.000|
11|Owner |2008-04-30 00:00:00.000|
12|Owner/Marketing Assistant |2008-04-30 00:00:00.000|
-- more --
Sample table: Person.Person
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
1|EM |false | |Ken |J |Sánchez | | 0| |[XML] |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
2|EM |false | |Terri |Lee |Duffy | | 1| |[XML] |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
3|EM |false | |Roberto | |Tamburello | | 0| |[XML] |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
4|EM |false | |Rob | |Walters | | 0| |[XML] |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
5|EM |false |Ms. |Gail |A |Erickson | | 0| |[XML] |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
6|EM |false |Mr. |Jossef |H |Goldberg | | 0| |[XML] |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
7|EM |false | |Dylan |A |Miller | | 2| |[XML] |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
8|EM |false | |Diane |L |Margheim | | 0| |[XML] |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
9|EM |false | |Gigi |N |Matthew | | 0| |[XML] |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
-- more --
Sample Solution:
-- Selecting BusinessEntityID, LastName, and FirstName from multiple tables based on specified conditions
SELECT pp.BusinessEntityID, LastName, FirstName
-- Retrieving BusinessEntityID, LastName, and FirstName columns
FROM Person.BusinessEntityContact AS pb
-- Joining Person.BusinessEntityContact with Person.ContactType based on ContactTypeID
INNER JOIN Person.ContactType AS pc
ON pc.ContactTypeID = pb.ContactTypeID
-- Joining Person.BusinessEntityContact with Person.Person based on BusinessEntityID
INNER JOIN Person.Person AS pp
ON pp.BusinessEntityID = pb.PersonID
-- Filtering the results to include only records where the ContactType Name is 'Purchasing Manager'
WHERE pc.Name = 'Purchasing Manager'
-- Sorting the results by LastName and FirstName
ORDER BY LastName, FirstName;
Explanation:
- The SELECT statement retrieves the BusinessEntityID, LastName, and FirstName columns from multiple tables.
- pp.BusinessEntityID, LastName, FirstName: Specifies the columns to be retrieved.
- The FROM clause specifies the source tables for the data, which are Person.BusinessEntityContact (aliased as pb) and Person.ContactType (aliased as pc).
- The INNER JOIN clauses join the Person.BusinessEntityContact table with the Person.ContactType table based on the ContactTypeID column and with the Person.Person table based on the BusinessEntityID column.
- The ON keyword is used to specify the join conditions.
- The WHERE clause filters the rows to include only those where the ContactType Name is 'Purchasing Manager'.
- pc.Name = 'Purchasing Manager': Filters the rows based on the Name column in the Person.ContactType table.
- The ORDER BY clause sorts the results by LastName and FirstName in ascending order.
- ORDER BY LastName, FirstName: Orders the results first by LastName and then by 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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-20.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics