w3resource
SQL exercises

SQL JOINS on HR Database: Display the department name, full name of manager, and their city

SQL JOINS on HR Database: Exercise-22 with Solution

22. Write a query in SQL to display the department name, full name (first and last name) of manager, and their city.

Sample table: employees


Sample table: departments


Sample table: locations


Sample Solution:

SELECT department_name, first_name || ' ' || last_name AS name_of_manager, city 
	FROM departments D 
		JOIN employees E 
			ON (D.manager_id=E.employee_id) 
				JOIN locations L USING (location_id);

Sample Output:

department_name		name_of_manager		city
Executive		Steven King		Seattle
IT			Alexander Hunold	Southlake
Finance			Nancy Greenberg		Seattle
Purchasing		Den Raphaely		Seattle
Shipping		Adam Fripp		South San Francisco
Sales			John Russell		OX9 9ZB
Administration		Jennifer Whalen		Seattle
Marketing		Michael Hartstein	Toronto
Human Resources		Susan Mavris		London
Public Relations	Hermann Baer		Munich
Accounting		Shelley Higgins		Seattle

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the department name, full name of manager, and their city - Duration

Rows:

Query visualization of Display the department name, full name of manager, and their city - Rows

Cost:

Query visualization of Display the department name, full name of manager, and their city - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to display the country name, city, and number of those departments where at leaste 2 employees are working.
Next: Write a query in SQL to display the employee ID, job name, number of days worked in for all those jobs in department 80.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming