MySQL String Exercises: Write a query to get the job_id and related employee's id
MySQL String: Exercise-1 with Solution
Write a MySQL query to get the job_id and related employee's id.
Sample table: employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | .......... | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Code:
-- This SQL query selects the job_id and concatenates the employee_id values separated by a space for each group of job_id.
SELECT
job_id,
GROUP_CONCAT(employee_id, ' ') AS 'Employees ID' -- Concatenates employee_ids with a space separator and renames the resulting column as 'Employees ID'
FROM
employees
GROUP BY
job_id; -- Groups the result by job_id, so each row represents a unique job_id with concatenated employee_ids.
Explanation:
- SELECT job_id, GROUP_CONCAT(employee_id, ' ') AS 'Employees ID': This part of the query selects the job_id column and concatenates the employee_id values for each group of job_id using the GROUP_CONCAT function. The 'Employees ID' alias is assigned to the concatenated string.
- FROM employees: Specifies the table from which to retrieve the data, in this case, the table named 'employees'.
- GROUP BY job_id: Groups the result set by the job_id column, so that the aggregation functions like GROUP_CONCAT are applied to each distinct job_id group separately. This ensures that each row in the result set represents a unique job_id with its corresponding concatenated employee_ids.
Sample Output:
job_id Employees ID AC_ACCOUNT 206 AC_MGR 205 AD_ASST 200 AD_PRES 100 AD_VP 101 ,102 FI_ACCOUNT 109 ,110 ,111 ,112 ,113 FI_MGR 108 HR_REP 203 IT_PROG 103 ,104 ,105 ,106 ,107 MK_MAN 201 MK_REP 202 PR_REP 204 PU_CLERK 115 ,116 ,117 ,118 ,119 PU_MAN 114 SA_MAN 145 ,146 ,147 ,148 ,149 SA_REP 150 ,151 ,152 ,153 ,154 ,155 ,156 ,157 ,158 ,159 ,160 ,161 ,162 ,163 ,164 ,165 ,166 ,167 ,168 ,169 ,170 ,171 ,172 ,173 ,174 ,175 ,176 ,177 ,178 ,179 SH_CLERK 180 ,181 ,182 ,183 ,184 ,185 ,186 ,187 ,188 ,189 ,190 ,191 ,192 ,193 ,194 ,195 ,196 ,197 ,198 ,199 ST_CLERK 125 ,126 ,127 ,128 ,129 ,130 ,131 ,132 ,133 ,134 ,135 ,136 ,137 ,138 ,139 ,140 ,141 ,142 ,143 ,144 ST_MAN 120 ,121 ,122 ,123 ,124
Go to:
PREV :MySQL string
NEXT :Write a MySQL query to update the portion of the phone_number in the employees table, within the phone number the substring '124' will be replaced by '999'.
MySQL Code Editor:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?