w3resource

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:

SELECT job_id, ARRAY_AGG(employee_id) 
FROM employees 
GROUP BY job_id;
job_id Employees ID
AC_ACCOUNT 206
AC_MGR 205
AD_ASST 200
AD_PRES 100
AD_VP 101 ,102
FI_ACCOUNT 110 ,113 ,111 ,109 ,112

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   
------------+-----------------------------------------------------------------------------------------------------------------------
 Salesman   | {122,123,124,120,121}
 AC_ACCOUNT | {206}
 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     | {156,157,158,159,160,161,162,164,174,175,150,151,176,177,178,179,165,152,153,154,155,166,167,168,169,170,171,172,173}
 ST_CLERK   | {133,134,135,136,138,139,140,141,142,143,144,137,125,126,127,128,1:

Practice Online


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

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming