AdventureWorks Database: Find email addresses of employees and groups them by city
SQL Query - AdventureWorks: Exercise-75 with Solution
75. From the following table write a query in SQL to find the email addresses of employees and groups them by city. Return top ten rows.
Sample table: Person.BusinessEntityAddress
businessentityid|addressid|addresstypeid|rowguid |modifieddate |
----------------+---------+-------------+------------------------------------+-----------------------+
1| 249| 2|3a5d0a00-6739-4dfe-a8f7-844cd9dee3df|2014-09-12 11:15:06.967|
2| 293| 2|84ae7057-edf4-4c51-8b8d-3aeaefbfb4a1|2014-09-12 11:15:06.967|
3| 224| 2|3c915b31-7c05-4a05-9859-0df663677240|2014-09-12 11:15:06.967|
4| 11387| 2|3dc70cc4-3ae8-424f-8b1f-481c5478e941|2014-09-12 11:15:06.967|
5| 190| 2|c0ed2f68-937b-4594-9459-581ac53c98e3|2014-09-12 11:15:06.967|
6| 286| 2|4ca1686a-a7df-4bd8-9d7d-82a63210208a|2014-09-12 11:15:06.967|
7| 49| 2|1528e305-3e34-4dea-bdd7-c7ddcdd11ef8|2014-09-12 11:15:06.967|
8| 230| 2|38f80f8f-5ca7-4d06-aefa-cd930a0a7b3f|2014-09-12 11:15:06.967|
9| 187| 2|51c9d232-dd34-49a5-8442-f269e0b9a6ff|2014-09-12 11:15:06.967|
10| 11386| 2|13981fc6-9688-49c8-aa1e-80c7f28ea2ff|2014-09-12 11:15:06.967|
11| 32505| 2|1f216434-3714-4bfb-9b05-4be77ebcce3f|2014-09-12 11:15:06.967|
12| 1| 2|8aa698fc-090f-42ee-a197-2e7f7394d9f1|2014-09-12 11:15:06.967|
-- more --
Sample table: Person.Address
|addressid|addressline1 |addressline2|city |stateprovinceid|postalcode|spatiallocation |rowguid |modifieddate | |---------|--------------------------|------------|-------------|---------------|----------|--------------------------------------------|------------------------------------|-----------------------| |1 |1970 Napa Ct. | |Bothell |79 |98011 |E6100000010CAE8BFC28BCE4474067A89189898A5EC0|9aadcb0d-36cf-483f-84d8-585c2d4ec6e9|2007-12-04 00:00:00.000| |2 |9833 Mt. Dias Blv. | |Bothell |79 |98011 |E6100000010CD6FA851AE6D74740BC262A0A03905EC0|32a54b9e-e034-4bfb-b573-a71cde60d8c0|2008-11-30 00:00:00.000| |3 |7484 Roundtree Drive | |Bothell |79 |98011 |E6100000010C18E304C4ADE14740DA930C7893915EC0|4c506923-6d1b-452c-a07c-baa6f5b142a4|2013-03-07 00:00:00.000| |4 |9539 Glenside Dr | |Bothell |79 |98011 |E6100000010C813A0D5F9FDE474011A5C28A7C955EC0|e5946c78-4bcc-477f-9fa1-cc09de16a880|2009-02-03 00:00:00.000| |5 |1226 Shoe St. | |Bothell |79 |98011 |E6100000010C61C64D8ABBD94740C460EA3FD8855EC0|fbaff937-4a97-4af0-81fd-b849900e9bb0|2008-12-19 00:00:00.000| |6 |1399 Firestone Drive | |Bothell |79 |98011 |E6100000010CE0B4E50458DA47402F12A5F80C975EC0|febf8191-9804-44c8-877a-33fde94f0075|2009-02-13 00:00:00.000| |7 |5672 Hale Dr. | |Bothell |79 |98011 |E6100000010C18E304C4ADE1474011A5C28A7C955EC0|0175a174-6c34-4d41-b3c1-4419cd6a0446|2009-12-11 00:00:00.000| |8 |6387 Scenic Avenue | |Bothell |79 |98011 |E6100000010C0029A5D93BDF4740E248962FD5975EC0|3715e813-4dca-49e0-8f1c-31857d21f269|2008-12-17 00:00:00.000| |9 |8713 Yosemite Ct. | |Bothell |79 |98011 |E6100000010C6A80AD742DDC4740851574F7198C5EC0|268af621-76d7-4c78-9441-144fd139821a|2012-05-30 00:00:00.000| |10 |250 Race Court | |Bothell |79 |98011 |E6100000010C219D64AE1FE4474040862564B7825EC0|0b6b739d-8eb6-4378-8d55-fe196af34c04|2008-12-02 00:00:00.000| -- more --Sample table: Person.EmailAddress
businessentityid|emailaddressid|emailaddress |rowguid |modifieddate |
----------------+--------------+-------------------------------------------+------------------------------------+-----------------------+
1| 1|[email protected] |8a1901e4-671b-431a-871c-eadb2942e9ee|2009-01-07 00:00:00.000|
2| 2|[email protected] |b5ff9efd-72a2-4f87-830b-f338fdd4d162|2008-01-24 00:00:00.000|
3| 3|[email protected] |c8a51084-1c03-4c58-a8b3-55854ae7c499|2007-11-04 00:00:00.000|
4| 4|[email protected] |17703ed1-0031-4b4a-afd2-77487a556b3b|2007-11-28 00:00:00.000|
5| 5|[email protected] |e76d2ea3-08e5-409c-bbe2-5dd1cdf89a3b|2007-12-30 00:00:00.000|
6| 6|[email protected] |a9c4093a-4f4a-4cad-bbb4-2c4e920baccb|2013-12-16 00:00:00.000|
7| 7|[email protected] |70429de4-c3bf-4f19-a00a-e976c8017fb3|2009-02-01 00:00:00.000|
8| 8|[email protected] |37f02a87-058d-49f8-a20d-965738b0a71f|2008-12-22 00:00:00.000|
9| 9|[email protected] |f888a16d-0c33-459e-9d72-d16ae0bb1f43|2009-01-09 00:00:00.000|
10| 10|[email protected] |e0dd366d-433d-4f5a-9347-1a5fe7fbe0a3|2009-04-26 00:00:00.000|
11| 11|[email protected] |0ff9523d-f398-4237-85f8-2834de441692|2010-11-28 00:00:00.000|
12| 12|[email protected] |b2962849-cc5f-4e57-bcb4-019642bbd8ed|2007-12-04 00:00:00.000|
13| 13|[email protected] |64871268-3812-402f-8a91-c618b6515b06|2010-12-16 00:00:00.000|
14| 14|[email protected] |bea9075c-1bed-4e5e-8234-f5641faf814c|2010-12-23 00:00:00.000|
15| 15|[email protected] |5cd782ba-f5ab-41ec-b206-09b06f52c96b|2011-01-11 00:00:00.000|
-- more --
Sample Solution:
-- Selecting the 'City' column and concatenating email addresses associated with each city,
-- separated by a semicolon, and aliasing the result as 'emails'
SELECT City, STRING_AGG(cast(EmailAddress as varchar(10485760)), ';') AS emails
-- From the Person schema's BusinessEntityAddress table as BEA
FROM Person.BusinessEntityAddress AS BEA
-- Joining with the Person schema's Address table as A on AddressID to retrieve address details
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
-- Joining with the Person schema's EmailAddress table as EA on BusinessEntityID to retrieve email addresses
INNER JOIN Person.EmailAddress AS EA
ON BEA.BusinessEntityID = EA.BusinessEntityID
-- Grouping the results by the 'City' column
GROUP BY City
-- Limiting the number of rows returned to 10
limit 10;
Explanation:
- The SQL query retrieves data from the BusinessEntityAddress, Address, and EmailAddress tables within the Person schema.
- It selects the 'City' column and concatenates email addresses associated with each city.
- The STRING_AGG() function concatenates email addresses, cast as varchar(10485760) (a very large varchar type to accommodate potentially long email lists), separated by a semicolon.
- The result of the concatenation is aliased as 'emails'.
- The tables are joined based on their corresponding keys (AddressID and BusinessEntityID).
- The results are grouped by the 'City' column to aggregate email addresses for each city.
- Finally, the LIMIT clause limits the number of rows returned to 10.
Sample Output:
city |emails | -----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Ballard |[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];isa| Baltimore |[email protected] | Barstow |[email protected] | Basingstoke Hants|[email protected];[email protected] | Baytown |[email protected] | Beaverton |[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected]| Bell Gardens |[email protected] | Bellevue |[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];jason0| Bellflower |[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];xavier18@adventure-w| Bellingham |[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];ja|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return names and modified date separated by commas.
Next: Replace string 'Supervisor' to 'Assistant' in jobtitle.
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-75.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics