SQL Exercise: Department name and the full name of the manager
18. From the following tables, write a SQL query to find the department name and the full name (first and last name) of the manager.
Sample table: departments
Sample table: employees
Sample Solution:
Sample Output:
department_name name_of_manager Executive Steven King IT Alexander Hunold Finance Nancy Greenberg Purchasing Den Raphaely Shipping Adam Fripp Sales John Russell Administration Jennifer Whalen Marketing Michael Hartstein Human Resources Susan Mavris Public Relations Hermann Baer Accounting Shelley Higgins
Code Explanation:
The above query in SQL that selects the department name and the name of the manager of each department from the 'departments' table and 'employees' table, respectively, where the manager_id in the 'departments' table matches the employee_id in the 'employees' table. The "||" operator is used to concatenate the first name, a space, and the last name of the manager.
The JOIN clause joins the departments table (aliased as D) and the employees table (aliased as E) on the condition that the manager_id in the departments table matches the employee_id in the employees table. This ensures that we only get the name of the manager for each department.
Visual Presentation:

Alternative Solutions:
Using INNER JOIN with Aliases:
Explanation:
This query uses INNER JOINs with aliases (d for 'departments', e for 'employees'). It combines the two tables based on matching keys ("manager_id" and "employee_id") and selects the department name and the name of the manager.
Using INNER JOIN with Explicit Column Names:
Explanation:
This query uses INNER JOINs but explicitly specifies column names. It performs INNER JOINs based on matching keys and selects the department name and the name of the manager.
Practice Online
Query Visualization:
Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Display country, city, and the departments.
Next SQL Exercise: Display the job title and average salary of employees.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.