w3resource

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

MySQL Joins: Exercise-8 with Solution

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

Sample table: employees


Sample table: departments


Code:

-- This SQL query retrieves specific columns from the 'departments' and 'employees' tables to get information about department managers.

SELECT 
    d.department_id, -- Selecting the 'department_id' column from the 'departments' table.
    d.department_name, -- Selecting the 'department_name' column from the 'departments' table.
    d.manager_id, -- Selecting the 'manager_id' column from the 'departments' table.
    e.first_name -- Selecting the 'first_name' column from the 'employees' table and aliasing it as 'e'.
FROM 
    departments d -- Specifying the 'departments' table and aliasing it as 'd'.
INNER JOIN 
    employees e -- Performing an inner join with the 'employees' table and aliasing it as 'e'.
ON 
    (d.manager_id = e.employee_id); -- Joining the 'departments' and 'employees' tables based on the 'manager_id' column to associate departments with their managers.

Explanation:

  • This SQL query retrieves specific columns ('department_id', 'department_name', 'manager_id', 'first_name') from the 'departments' and 'employees' tables to get information about department managers.
  • It performs an inner join between the 'departments' and 'employees' tables based on the 'manager_id' column to associate each department with its manager.
  • The columns selected for the output include the department ID, department name, manager ID, and manager's first name.

Relational Algebra Expression:

Relational Algebra Expression: Join: Display the department ID and  name and first name of manager.

Relational Algebra Tree:

Relational Algebra Tree: Join: Display the department ID and  name and first name of manager.

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 :

hr database

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

Previous:Write a MySQL 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 MySQL query to display the department name, manager name, and city.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.