w3resource

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:

SELECT employee_id, first_name, last_name, 
(SELECT department_name 
FROM departments d 
WHERE e.department_id = d.department_id) 
department FROM employees e 
ORDER BY department;

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?



Share this Tutorial / Exercise on : Facebook and Twitter