AdventureWorks Database: Retrieve name and city of the employees
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics