﻿ AdventureWorks: Retrieve name and city of the employees

# 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

Click to view Full table

Sample table: HumanResources.Employee

Click to view Full table

Sample table: Person.Address

Click to view Full table

Sample table: Person.BusinessEntityAddress

Click to view Full table

Sample Solution:

``````SELECT concat(RTRIM(p.FirstName),' ', LTRIM(p.LastName)) AS Name, d.City
FROM Person.Person AS p
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN
(SELECT bea.BusinessEntityID, a.City
FROM Person.Address AS a
INNER JOIN Person.BusinessEntityAddress AS bea
ON a.AddressID = bea.AddressID) AS d
ON p.BusinessEntityID = d.BusinessEntityID
ORDER BY p.LastName, p.FirstName;
``````

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

Share this Tutorial / Exercise on : Facebook and Twitter

## SQL: Tips of the Day

How to create a foreign key in SQL Server?

```create table question_bank
(
question_id uniqueidentifier primary key,
question_exam_id uniqueidentifier not null,
question_text varchar(1024) not null,
question_point_value decimal,
constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);
```

Database: SQL Server

Ref : https://bit.ly/3sgS152