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