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.
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 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:
-- 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'.
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://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-58.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics