w3resource

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:

SQL Exercises: Display the department name, full name  of manager, and their city.

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: 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.

What is the difficulty level of this exercise?