AdventureWorks Database: List contacts who are Purchasing Manager
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.