PostgreSQL String() Function: Find the employee Id(s) for a particular post

1. Write a query to get the job_id and the ID(s) for those employees who is working in that post.

Sample Solution:

Code:

-- Selecting job_id and creating an array of employee_ids for each job
SELECT job_id, ARRAY_AGG(employee_id) 
-- From the employees table
FROM employees 
-- Grouping the results by job_id
GROUP BY job_id;

Explanation:

• This SQL query selects data from the employees table.
• It groups the data by the job_id column.
• For each group of job_id, it aggregates the corresponding employee_id values into an array using the ARRAY_AGG function.
• The result is a list where each job_id is associated with an array of employee_ids who have that particular job.

Sample table: employees

Output:

pg_exercises=# SELECT job_id, ARRAY_AGG(employee_id)
pg_exercises-# FROM employees
pg_exercises-# GROUP BY job_id;
   job_id   |                                                       array_agg   
------------+-----------------------------------------------------------------------------------------------------------------------
 AC_ACCOUNT | {206}
 ST_MAN	    | {120,121,122,123,124}
 IT_PROG    | {103,104,105,106,107}
 SA_MAN     | {145,146,147,148,149}
 AD_PRES    | {100}
 AC_MGR     | {205}
 FI_MGR     | {108}
 AD_ASST    | {200}
 MK_MAN     | {201}
 PU_CLERK   | {115,116,117,118,119}
 HR_REP     | {203}
 PR_REP     | {204}
 FI_ACCOUNT | {109,110,111,112,113}
 SH_CLERK   | {180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199}
 AD_VP      | {101,102}
 SA_REP     | {150,151,152,153,154,155,156,157,158,159,160,161,162,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179}
 ST_CLERK   | {133,134,135,136,137,138,139,140,129,130,131,132,141,142,143,144,125,126,127,128}
 MK_REP	    |	{202}
 PU_MAN	    |	{114}

Practice Online

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

Previous: PostgreSQL String() Function - Exercises, Practice, Solution
Next: Write a query to update the phone_number column with '999' where the substring '124' found in that column.