MySQL String Exercises: Display leading zeros before maximum and minimum salary

MySQL String: Exercise-4 with Solution

Write a MySQL query to display leading zeros before maximum and minimum salary.

+------------+---------------------------------+------------+------------+
| JOB_ID     | JOB_TITLE                       | MIN_SALARY | MAX_SALARY |
+------------+---------------------------------+------------+------------+
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
+------------+---------------------------------+------------+------------+

Code:

-- This SQL query selects the job_id, maximum salary, and minimum salary from the jobs table. 
-- The LPAD function is used to left-pad the salary values with zeros to ensure they have a total width of 7 characters.

SELECT 
job_id,  
LPAD(max_salary, 7, '0') AS ' Max Salary', -- Left-pads the max_salary column with zeros to ensure a width of 7 characters, and renames the resulting column as 'Max Salary'.
LPAD(min_salary, 7, '0') AS ' Min Salary'  -- Left-pads the min_salary column with zeros to ensure a width of 7 characters, and renames the resulting column as 'Min Salary'.
FROM 
jobs;

Explanation:

• SELECT job_id, LPAD(max_salary, 7, '0') AS ' Max Salary', LPAD(min_salary, 7, '0') AS ' Min Salary': This part of the query selects the job_id column from the jobs table along with the maximum and minimum salary columns. It uses the LPAD function to left-pad the salary values with zeros to ensure they have a total width of 7 characters. The AS keyword is used to assign aliases to the resulting columns, renaming them as 'Max Salary' and 'Min Salary' respectively.
• FROM jobs: Specifies the table from which to retrieve the data, in this case, the table named 'jobs'.

Sample Output:

job_id		Max Salary	Min Salary
AD_PRES		0040000		0020000
AD_VP		0030000		0015000
AD_ASST		0006000		0003000
FI_MGR		0016000		0008200
FI_ACCOUNT	0009000		0004200
AC_MGR		0016000		0008200
AC_ACCOUNT	0009000		0004200
SA_MAN		0020000		0010000
SA_REP		0012000		0006000
PU_MAN		0015000		0008000
PU_CLERK	0005500		0002500
ST_MAN		0008500		0005500
ST_CLERK	0005000		0002000
SH_CLERK	0005500		0002500
IT_PROG		0010000		0004000
MK_MAN		0015000		0009000
MK_REP		0009000		0004000
HR_REP		0009000		0004000
PR_REP		0010500		0004500

MySQL Code Editor:

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

Previous:Write a MySQL query to get the details of the employees where the length of the first name greater than or equal to 8.
Next:Write a MySQL query to append '@example.com' to email field.