SQL Exercise: Display the department, manager name, 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:

-- Selecting specific columns (department_name, first_name || ' ' || last_name AS name_of_manager, city) from the 'departments' table, aliased as 'D', the 'employees' table, aliased as 'E', and the 'locations' table, aliased as 'L'
SELECT department_name, first_name || ' ' || last_name AS name_of_manager, city 

-- Performing an INNER JOIN between the 'departments' table (aliased as 'D') and the 'employees' table (aliased as 'E') based on the condition that 'D.manager_id' is equal to 'E.employee_id'
FROM departments D 

JOIN employees E 
  ON (D.manager_id = E.employee_id) 

-- Performing another INNER JOIN between the result set and the 'locations' table (aliased as 'L') using the common column 'location_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

Code Explanation:

The above query in SQL that will return a list of department names, the full name of the department manager, and the city where the department is located.
The JOIN clause joins the departments table with the employees table on the manager ID, and then joins the locations table using the location ID.
In this case, the results will include only the departments that have a manager, and not the departments that have no location and do not have a manager.

Visual Presentation:

Alternative Solutions:

Using INNER JOIN with Explicit Column Names:

SELECT departments.department_name, employees.first_name || ' ' || employees.last_name AS name_of_manager, locations.city 
FROM departments
JOIN employees ON departments.manager_id = employees.employee_id
JOIN locations ON departments.location_id = locations.location_id;

Explanation:

This query uses INNER JOINs with explicitly specifies column names. It performs INNER JOINs based on matching keys (manager_id and location_id) to retrieve the department name, manager's name, and city.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Departments where at least 2 employees are working.
Next SQL Exercise: Number of days worked for all jobs in department 80.