MySQL Joins Exercises: Display the department ID and name and first name of manager

MySQL Joins: Exercise-8 with Solution

Write a query to display the department ID and name and first name of manager.

Sample table: employees

Sample table: departments

Code:

SELECT d.department_id, d.department_name, d.manager_id, e.first_name 
FROM departments d 
INNER JOIN employees e 
ON (d.manager_id = e.employee_id);

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

department_id	department_name	  manager_id	first_name
10		Administration	  200		Jennifer
20		Marketing	  201		Michael
30		Purchasing	  114		Den
40		Human Resources	  203		Susan
50		Shipping	  121		Adam
60		IT		  103		Alexander
70		Public Relations  204		Hermann
80		Sales		  145		John
90		Executive	  100		Steven
100		Finance		  108		Nancy
110		Accounting	  205		Shelley

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 find the employee ID, job title, number of days between ending date and starting date for all jobs in department 90 from job history.
Next:Write a query to display the department name, manager name, and city.