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. From the following tables, write a SQL query to find the department name, full name (first and last name) of the 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

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: From the following tables, write a SQL query to find those departments where at least 2 employees work. Group the result set on country name and city. Return country name, city, and number of departments.
Next: From the following tables, write a SQL query to compute the number of days worked by employees in a department of ID 80. Return employee ID, job title, number of days worked.