w3resource

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.

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.



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