SQL Exercise: Department name and the full name of the manager

Last update on December 20 2024 11:42:10 (UTC/GMT +8 hours)

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:

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

-- 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);

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:

SELECT d.department_name, CONCAT(e.first_name, ' ', e.last_name) AS name_of_manager
FROM departments d
JOIN employees e ON d.manager_id = e.employee_id;

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:

SELECT departments.department_name, CONCAT(employees.first_name, ' ', employees.last_name) AS name_of_manager
FROM departments
JOIN employees ON departments.manager_id = employees.employee_id;

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.