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 ------------+----------------------------------------------------------------------------------------------------------------------- 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.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework