AdventureWorks Database: Find email addresses of employees and groups them by city
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics