AdventureWorks Database: Employee name, email separated by a new line

SQL Query - AdventureWorks: Exercise-58 with Solution

58. From the following table write a SQL query to output an employee's name and email address, separated by a new line character.

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 --

Click to view Full table

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 --

Click to view Full table

Sample Solution:

-- Concatenating the first name and last name with a space between them, and then concatenating the email address with a line break character
SELECT concat(p.FirstName,' ', p.LastName) || ' '|| chr(10)|| pe.EmailAddress   

-- From the Person schema's Person table, aliasing it as 'p'
FROM Person.Person p 

-- Performing an inner join with the EmailAddress table based on BusinessEntityID
-- Only including rows where BusinessEntityID matches '1' in both tables
INNER JOIN Person.EmailAddress pe ON p.BusinessEntityID = pe.BusinessEntityID  
  AND p.BusinessEntityID = 1;

Explanation:

• The SQL query concatenates the first name, last name, and email address for a specific person, adding a line break between the full name and the email address.
• It retrieves data from the Person table within the Person schema.
• The INNER JOIN clause is used to combine rows from the Person table (aliased as 'p') with matching rows from the EmailAddress table based on the common column BusinessEntityID.
• The ON clause specifies the condition for the join: p.BusinessEntityID = pe.BusinessEntityID ensures that rows are joined based on the matching BusinessEntityID, and p.BusinessEntityID = 1 restricts the join to only include rows where BusinessEntityID is equal to '1' in both tables.
• The concat() function concatenates the first name and last name with a space between them, and then concatenates the email address with a line break character (chr(10)).

Sample Output:

?column?                             |
-------------------------------------+
Ken Sánchez ¶[email protected]| 

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Product with prefixes of either chain or full.
Next: Find the position of the string 'yellow'.