PostgreSQL Subquery: Display the employee ID, first name, last name and department name of all employees
13. Write a SQL subquery to find the employee ID, first name, last name and department names of all employees.
Sample Solution:
Code:
-- This SQL query retrieves the employee ID, first name, last name, and department name of employees, ordered by department name.
SELECT employee_id, -- Selects the employee_id column from the employees table
first_name, -- Selects the first_name column from the employees table
last_name, -- Selects the last_name column from the employees table
(SELECT department_name -- Subquery: Selects the department_name associated with the department_id of the current employee
FROM departments d
WHERE e.department_id = d.department_id) -- Matches the department_id of the current employee (aliased as 'e') with the department_id in the departments table (aliased as 'd')
AS department -- Renames the subquery result column as 'department'
FROM employees e -- Specifies the table from which to retrieve data (aliased as 'e'), in this case, the employees table
ORDER BY department; -- Orders the results by department name
Explanation:
- This SQL query retrieves the employee ID, first name, last name, and department name of employees, ordered by department name.
- The outermost SELECT statement selects the employee ID, first name, last name, and the department name associated with each employee.
- The subquery selects the department name from the departments table (aliased as 'd') where the department_id matches the department_id of the current employee (aliased as 'e').
- The result of the subquery is then used as the 'department' column in the main query.
- The ORDER BY clause sorts the result set by department name.
Sample table: employees
Sample table: departments
Output:
pg_exercises=# SELECT employee_id, first_name, last_name, pg_exercises-# (SELECT department_name pg_exercises(# FROM departments d pg_exercises(# WHERE e.department_id = d.department_id) pg_exercises-# department FROM employees e pg_exercises-# ORDER BY department; employee_id | first_name | last_name | department -------------+-------------+-------------+------------------ 206 | William | Gietz | Accounting 205 | Shelley | Higgins | Accounting 200 | Jennifer | Whalen | Administration 102 | Lex | De Haan | Executive 100 | Steven | King | Executive 101 | Neena | Kochhar | Executive 108 | Nancy | Greenberg | Finance 110 | John | Chen | Finance 111 | Ismael | Sciarra | Finance 112 | Jose Manuel | Urman | Finance 109 | Daniel | Faviet | Finance 113 | Luis | Popp | Finance 203 | Susan | Mavris | Human Resources 103 | Alexander | Hunold | IT 107 | Diana | Lorentz | IT 104 | Bruce | Ernst | IT 105 | David | Austin | IT 106 | Valli | Pataballa | IT 201 | Michael | Hartstein | Marketing 202 | Pat | Fay | Marketing 204 | Hermann | Baer | Public Relations ... | ... | ... | ... 187 | Anthony | Cabrio | Shipping 188 | Kelly | Chung | Shipping 124 | Kevin | Mourgos | Shipping 178 | Kimberely | Grant | (106 rows)
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL subquery to find the first_name and last_name of the employees who are not supervisors.
Next: Write a SQL subquery to find the employee ID, first name, last name and salary of all employees whose salary is above the average salary for their departments.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/postgresql-exercises/subquery/postgresql-subquery-exercise-13.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics