AdventureWorks Database: Retrieve name and city of the employees
SQL Query - AdventureWorks: Exercise-42 with Solution
42. Write a query in SQL to find the employee's full name (firstname and lastname) and city from the following tables. Order the result set on lastname then by firstname.
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: HumanResources.Employee
businessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
1|295847284 |adventure-works\ken0 |Chief Executive Officer |1969-01-29|S |M |2009-01-14|true | 99| 69|true |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/ |
2|245797967 |adventure-works\terri0 |Vice President of Engineering |1971-08-01|S |F |2008-01-31|true | 1| 20|true |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/ |
3|509647174 |adventure-works\roberto0 |Engineering Manager |1974-11-12|M |M |2007-11-11|true | 2| 21|true |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/ |
4|112457891 |adventure-works\rob0 |Senior Tool Designer |1974-12-23|S |M |2007-12-05|false | 48| 80|true |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/ |
5|695256908 |adventure-works\gail0 |Design Engineer |1952-09-27|M |F |2008-01-06|true | 5| 22|true |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/ |
6|998320692 |adventure-works\jossef0 |Design Engineer |1959-03-11|M |M |2008-01-24|true | 6| 23|true |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/ |
7|134969118 |adventure-works\dylan0 |Research and Development Manager |1987-02-24|M |M |2009-02-08|true | 61| 50|true |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/ |
8|811994146 |adventure-works\diane1 |Research and Development Engineer |1986-06-05|S |F |2008-12-29|true | 62| 51|true |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/ |
9|658797903 |adventure-works\gigi0 |Research and Development Engineer |1979-01-21|M |F |2009-01-16|true | 63| 51|true |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/ |
10|879342154 |adventure-works\michael6 |Research and Development Manager |1984-11-30|M |M |2009-05-03|true | 16| 64|true |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/ |
-- 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.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 Solution:
-- Selecting concatenated full names and cities
SELECT CONCAT(RTRIM(p.FirstName), ' ', LTRIM(p.LastName)) AS Name, d.City
-- From the Person schema's Person table, aliasing it as 'p'
FROM Person.Person AS p
-- Joining Person and Employee tables based on BusinessEntityID
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
-- Joining an inner query result with Person table based on BusinessEntityID
INNER JOIN
(
-- Selecting BusinessEntityID and City from Address and BusinessEntityAddress tables
SELECT bea.BusinessEntityID, a.City
FROM Person.Address AS a
INNER JOIN Person.BusinessEntityAddress AS bea
ON a.AddressID = bea.AddressID
) AS d
-- Joining the result of the inner query with Person table based on BusinessEntityID
ON p.BusinessEntityID = d.BusinessEntityID
-- Ordering the results by last name and first name
ORDER BY p.LastName, p.FirstName;
Explanation:
- The SQL query retrieves data from the Person table within the Person schema, specifically the FirstName and LastName columns.
- It concatenates the first name and last name to form the full name using the CONCAT function.
- The RTRIM and LTRIM functions are used to remove any leading or trailing spaces from the first name and last name, respectively.
- It also retrieves the city information from the Address table in the Person schema.
- The innermost query selects BusinessEntityID and City from the Address and BusinessEntityAddress tables, respectively.
- The inner query result is aliased as d.
- The outer query then joins the Person table with the Employee table based on the BusinessEntityID.
- It also joins the result of the inner query with the Person table based on the BusinessEntityID.
- The result set is ordered by last name and then first name.
- Comments provide explanatory notes for better understanding of the code.
Sample Output:
name |city | ------------------------+-------------+ Syed Abbas |Bothell | Kim Abercrombie |Carnation | Hazem Abolrous |Kenmore | Pilar Ackerman |Seattle | Jay Adams |Monroe | François Ajenstat |Issaquah | Amy Alberts |Renton | Greg Alderson |Bellevue | Sean Alexander |Renton | Gary Altman |Renton | Nancy Anderson |Sammamish | Pamela Ansman-Wolfe |Portland | Zainal Arifin |Issaquah | Dan Bacon |Issaquah | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Retrieve the territory name and BusinessEntityID.
Next: Fetch rows using a derived table.
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-42.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics