AdventureWorks Database: Retrieve the territory name and BusinessEntityID
SQL Query - AdventureWorks: Exercise-41 with Solution
41. From the following tables write a SQL query to retrieve the territory name and BusinessEntityID. The result set includes all salespeople, regardless of whether or not they are assigned a territory.
Sample table: Sales.SalesTerritory
territoryid|name |countryregioncode|group |salesytd |saleslastyear|costytd|costlastyear|rowguid |modifieddate |
-----------+--------------+-----------------+-------------+-------------+-------------+-------+------------+------------------------------------+-----------------------+
1|Northwest |US |North America| 7887186.7882| 3298694.4938| 0| 0|43689a10-e30b-497f-b0de-11de20267ff7|2008-04-30 00:00:00.000|
2|Northeast |US |North America| 2402176.8476| 3607148.9371| 0| 0|00fb7309-96cc-49e2-8363-0a1ba72486f2|2008-04-30 00:00:00.000|
3|Central |US |North America| 3072175.118| 3205014.0767| 0| 0|df6e7fd8-1a8d-468c-b103-ed8addb452c1|2008-04-30 00:00:00.000|
4|Southwest |US |North America|10510853.8739| 5366575.7098| 0| 0|dc3e9ea0-7950-4431-9428-99dbcbc33865|2008-04-30 00:00:00.000|
5|Southeast |US |North America| 2538667.2515| 3925071.4318| 0| 0|6dc4165a-5e4c-42d2-809d-4344e0ac75e7|2008-04-30 00:00:00.000|
6|Canada |CA |North America| 6771829.1376| 5693988.86| 0| 0|06b4af8a-1639-476e-9266-110461d66b00|2008-04-30 00:00:00.000|
7|France |FR |Europe | 4772398.3078| 2396539.7601| 0| 0|bf806804-9b4c-4b07-9d19-706f2e689552|2008-04-30 00:00:00.000|
8|Germany |DE |Europe | 3805202.3478| 1307949.7917| 0| 0|6d2450db-8159-414f-a917-e73ee91c38a9|2008-04-30 00:00:00.000|
9|Australia |AU |Pacific | 5977814.9154| 2278548.9776| 0| 0|602e612e-dfe9-41d9-b894-27e489747885|2008-04-30 00:00:00.000|
10|United Kingdom|GB |Europe | 5012905.3656| 1635823.3967| 0| 0|05fc7e1f-2dea-414e-9ecd-09d150516fb5|2008-04-30 00:00:00.000|
Sample table: Sales.SalesPerson
businessentityid|territoryid|salesquota|bonus|commissionpct|salesytd |saleslastyear|rowguid |modifieddate |
----------------+-----------+----------+-----+-------------+------------+-------------+------------------------------------+-----------------------+
274| | | 0| 0| 559697.5639| 0|48754992-9ee0-4c0e-8c94-9451604e3e02|2010-12-28 00:00:00.000|
275| 2| 300000| 4100| 0.012|3763178.1787| 1750406.4785|1e0a7274-3064-4f58-88ee-4c6586c87169|2011-05-24 00:00:00.000|
276| 4| 250000| 2000| 0.015|4251368.5497| 1439156.0291|4dd9eee4-8e81-4f8c-af97-683394c1f7c0|2011-05-24 00:00:00.000|
277| 3| 250000| 2500| 0.015|3189418.3662| 1997186.2037|39012928-bfec-4242-874d-423162c3f567|2011-05-24 00:00:00.000|
278| 6| 250000| 500| 0.01|1453719.4653| 1620276.8966|7a0ae1ab-b283-40f9-91d1-167abf06d720|2011-05-24 00:00:00.000|
279| 5| 300000| 6700| 0.01| 2315185.611| 1849640.9418|52a5179d-3239-4157-ae29-17e868296dc0|2011-05-24 00:00:00.000|
280| 1| 250000| 5000| 0.01|1352577.1325| 1927059.178|be941a4a-fb50-4947-bda4-bb8972365b08|2011-05-24 00:00:00.000|
281| 4| 250000| 3550| 0.01|2458535.6169| 2073505.9999|35326ddb-7278-4fef-b3ba-ea137b69094e|2011-05-24 00:00:00.000|
282| 6| 250000| 5000| 0.015|2604540.7172| 2038234.6549|31fd7fc1-dc84-4f05-b9a0-762519eacacc|2011-05-24 00:00:00.000|
283| 1| 250000| 3500| 0.012|1573012.9383| 1371635.3158|6bac15b2-8ffb-45a9-b6d5-040e16c2073f|2011-05-24 00:00:00.000|
284| 1| 300000| 3900| 0.019|1576562.1966| 0|ac94ec04-a2dc-43e3-8654-dd0c546abc17|2012-09-23 00:00:00.000|
285| | | 0| 0| 172524.4512| 0|cfdbef27-b1f7-4a56-a878-0221c73bae67|2013-03-07 00:00:00.000|
286| 9| 250000| 5650| 0.018|1421810.9242| 2278548.9776|9b968777-75dc-45bd-a8df-9cdaa72839e1|2013-05-23 00:00:00.000|
287| | | 0| 0| 519905.932| 0|1dd1f689-df74-4149-8600-59555eef154b|2012-04-09 00:00:00.000|
288| 8| 250000| 75| 0.018|1827066.7118| 1307949.7917|224bb25a-62e3-493e-acaf-4f8f5c72396a|2013-05-23 00:00:00.000|
289| 10| 250000| 5150| 0.02|4116871.2277| 1635823.3967|25f6838d-9db4-4833-9ddc-7a24283af1ba|2012-05-23 00:00:00.000|
290| 7| 250000| 985| 0.016|3121616.3202| 2396539.7601|f509e3d4-76c8-42aa-b353-90b7b8db08de|2012-05-23 00:00:00.000|
Sample Solution:
-- Selecting specific columns from the SalesTerritory table and SalesPerson table
SELECT st.Name AS Territory, sp.BusinessEntityID
-- From the Sales schema's SalesTerritory table, aliasing it as 'st'
FROM Sales.SalesTerritory AS st
-- Performing a right outer join between SalesTerritory and SalesPerson tables
-- All rows from the SalesPerson table will be included, and matching rows from the SalesTerritory table will be included
RIGHT OUTER JOIN Sales.SalesPerson AS sp
-- Joining SalesTerritory and SalesPerson tables based on TerritoryID
ON st.TerritoryID = sp.TerritoryID ;
Explanation:
- The SQL query retrieves data from the SalesTerritory table in the Sales schema and the SalesPerson table in the same schema.
- It selects two columns: Name from the SalesTerritory table (aliased as st) and BusinessEntityID from the SalesPerson table (aliased as sp).
- The RIGHT OUTER JOIN clause is used to combine rows from both tables based on a matching condition, which is the equality of TerritoryID between SalesTerritory and SalesPerson tables.
- This join type ensures that all rows from the right table (SalesPerson table) are included in the result set. If there are unmatched rows in the left table (SalesTerritory table), NULL values will be included for the columns from the left table.
- The result set will contain territories and their associated business entity IDs for salespeople, including territories that do not have any assigned salesperson (due to the right outer join).
- Comments provide explanatory notes for better understanding of the code.
Sample Output:
territory |businessentityid|
--------------+----------------+
| 274|
Northeast | 275|
Southwest | 276|
Central | 277|
Canada | 278|
Southeast | 279|
Northwest | 280|
Southwest | 281|
Canada | 282|
Northwest | 283|
Northwest | 284|
| 285|
Australia | 286|
| 287|
Germany | 288|
United Kingdom| 289|
France | 290|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Fetch product names, salesorderIDs using INNER JOIN.
Next: Retrieve name and city of the employees.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics