MySQL Joins Exercises: Display the department name, manager name, and city

MySQL Joins: Exercise-9 with Solution

Write a query to display the department name, manager name, and city.

Sample table: employees

Sample table: departments

Sample table: locations

Code:

SELECT d.department_name, e.first_name, l.city 
FROM departments d 
JOIN employees e 
ON (d.manager_id = e.employee_id) 
JOIN locations l USING (location_id);

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

department_name		first_name	city
Administration		Jennifer	Seattle
Marketing		Michael		Toronto
Purchasing		Den		Seattle
Human Resources		Susan		London
Shipping		Adam		South San Francisco
IT			Alexander	Southlake
Public Relations	Hermann		Munich
Sales			John		OX9 9ZB
Executive		Steven		Seattle
Finance			Nancy		Seattle
Accounting		Shelley		Seattle

MySQL Code Editor:

Structure of 'hr' database :

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

Previous:Write a query to display the department ID and name and first name of manager.
Next:Write a query to display the job title and average salary of employees.