MySQL String Exercises: Display the first name and salary for all employees
MySQL String: Exercise-15 with Solution
Write a MySQL query to display the first name and salary for all employees. Format the salary to be 10 characters long, left-padded with the $ symbol. Label the column SALARY.
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 first name of employees and left-pads their salary values with '$' characters up to a total width of 10 characters.
SELECT
first_name, -- Selecting the first name from the employees table.
-- Left-pads the salary values with '$' characters up to a total width of 10 characters.
LPAD(salary, 10, '$') SALARY
FROM
employees; -- Specifies the table from which data is being retrieved, in this case, it's the 'employees' table.
Explanation:
- The SELECT statement retrieves data from the specified table (employees).
- first_name column is selected directly.
- The LPAD() function left-pads the salary values with the specified character ('$') up to a total width of 10 characters.
- The LPAD() function ensures that the salary values are formatted consistently, with '$' characters added on the left if necessary to reach the specified width.
- The result is aliased as SALARY in the output.
Sample Output:
first_name SALARY Steven $$24000.00 Neena $$17000.00 Lex $$17000.00 Alexander $$$9000.00 Bruce $$$6000.00 David $$$4800.00 Valli $$$4800.00 Diana $$$4200.00 Nancy $$12000.00 Daniel $$$9000.00 John $$$8200.00 Ismael $$$7700.00 Jose Manuel $$$7800.00 Luis $$$6900.00 Den $$11000.00 Alexander $$$3100.00 Shelli $$$2900.00 Sigal $$$2800.00 Guy $$$2600.00 Karen $$$2500.00 Matthew $$$8000.00 Adam $$$8200.00 Payam $$$7900.00 Shanta $$$6500.00 Kevin $$$5800.00 Julia $$$3200.00 Irene $$$2700.00 James $$$2400.00 Steven $$$2200.00 Laura $$$3300.00 Mozhe $$$2800.00 James $$$2500.00 TJ $$$2100.00 Jason $$$3300.00 Michael $$$2900.00 Ki $$$2400.00 Hazel $$$2200.00 Renske $$$3600.00 Stephen $$$3200.00 John $$$2700.00 Joshua $$$2500.00 Trenna $$$3500.00 Curtis $$$3100.00 Randall $$$2600.00 Peter $$$2500.00 John $$14000.00 Karen $$13500.00 Alberto $$12000.00 Gerald $$11000.00 Eleni $$10500.00 Peter $$10000.00 David $$$9500.00 Peter $$$9000.00 Christopher $$$8000.00 Nanette $$$7500.00 Oliver $$$7000.00 Janette $$10000.00 Patrick $$$9500.00 Allan $$$9000.00 Lindsey $$$8000.00 Louise $$$7500.00 Sarath $$$7000.00 Clara $$10500.00 Danielle $$$9500.00 Mattea $$$7200.00 David $$$6800.00 Sundar $$$6400.00 Amit $$$6200.00 Lisa $$11500.00 Harrison $$10000.00 Tayler $$$9600.00 William $$$7400.00 Elizabeth $$$7300.00 Sundita $$$6100.00 Ellen $$11000.00 Alyssa $$$8800.00 Jonathon $$$8600.00 Jack $$$8400.00 Kimberely $$$7000.00 Charles $$$6200.00 Winston $$$3200.00 Jean $$$3100.00 Martha $$$2500.00 Girard $$$2800.00 Nandita $$$4200.00 Alexis $$$4100.00 Julia $$$3400.00 Anthony $$$3000.00 Kelly $$$3800.00 Jennifer $$$3600.00 Timothy $$$2900.00 Randall $$$2500.00 Sarah $$$4000.00 Britney $$$3900.00 Samuel $$$3200.00 Vance $$$2800.00 Alana $$$3100.00 Kevin $$$3000.00 Donald $$$2600.00 Douglas $$$2600.00 Jennifer $$$4400.00 Michael $$13000.00 Pat $$$6000.00 Susan $$$6500.00 Hermann $$10000.00 Shelley $$12000.00 William $$$8300.00
Go to:
PREV :Write a MySQL query that displays the first name and the length of the first name for all employees whose name starts with the letters 'A', 'J' or 'M'.
NEXT :Write a MySQL query to display the first eight characters of the employees' first names and indicates the amounts of their salaries with '$' sign. Each '$' sign signifies a thousand dollars. Sort the data in descending order of salary.
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?