PostgreSQL Basic SELECT Statement: Calculate the monthly salary of each employee
19. Write a query to get a monthly salary (rounded up to 2 decimal places) of each employee.
Note : Assume that, the salary field provides the 'annual salary' information.
Sample Solution:
Code:
-- Selecting the first name, last name, and rounding the annual salary divided by 12 to two decimal places to represent the monthly salary
SELECT first_name, last_name,
-- Calculating the monthly salary by dividing the annual salary by 12 and rounding the result to two decimal places, and labeling it as "Monthly Salary"
ROUND(salary/12,2) as "Monthly Salary"
-- Selecting data from the employees table
FROM employees;
Explanation:
- This SQL code selects the first name, last name, and calculates the monthly salary for each employee in the "employees" table.
- The salary is divided by 12 to get the monthly equivalent, and the result is rounded to two decimal places using the ROUND function.
- The result set will contain three columns: first name, last name, and the calculated monthly salary for each employee.
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 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Output:
pg_exercises=# SELECT first_name, last_name, pg_exercises-# ROUND(salary/12,2) as "Monthly Salary" pg_exercises-# FROM employees; first_name | last_name | Monthly Salary -------------+-------------+---------------- Steven | King | 2000.00 Neena | Kochhar | 1416.67 Lex | De Haan | 1416.67 Alexander | Hunold | 750.00 Bruce | Ernst | 500.00 David | Austin | 400.00 Valli | Pataballa | 400.00 Diana | Lorentz | 350.00 .... Den | Raphaely | 916.67 Alexander | Khoo | 258.33 Shelli | Baida | 241.67 Sigal | Tobias | 233.33 Guy | Himuro | 216.67 Karen | Colmenares | 208.33 Matthew | Weiss | 666.67 Adam | Fripp | 683.33 .... Steven | Markle | 183.33 Laura | Bissot | 275.00 Mozhe | Atkinson | 233.33 James | Marlow | 208.33 .... Stephen | Stiles | 266.67 John | Seo | 225.00 Joshua | Patel | 208.33 Trenna | Rajs | 291.67 ... Hermann | Baer | 835.83 Shelley | Higgins | 1002.50 William | Gietz | 694.17 (106 rows)
Go to:
PREV : Write a query to select first ten records from a table.
NEXT :PostgreSQL Restricting and Sorting Data - Exercises, Practice, Solution
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?