SQLite Exercise: Get monthly salary of each and every employee
Write a query to get monthly salary (round 2 decimal places) of each and every 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 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 1987-06-26 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | ........ | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
SQLite Code :
-- Selecting the "first_name," "last_name," and rounding the monthly salary calculated as salary/12, with 2 decimal places
SELECT first_name, last_name, ROUND(salary/12, 2) as 'Monthly Salary'
-- Specifying the table from which to retrieve the data, in this case, "employees"
FROM employees;
Output:
first_name last_name Monthly Sal ---------- ---------- ----------- Steven King 2000.0 Neena Kochhar 1416.0 Lex De Haan 1416.0 Alexander Hunold 750.0 Bruce Ernst 500.0 ... ... ... Pat Fay 500.0 Susan Mavris 541.0 Hermann Baer 833.0 Shelley Higgins 1000.0 William Gietz 691.0
Explanation:
The above SQLite query retrieves the "first_name" and "last_name" columns from the "employees" table and calculates the monthly salary for each employee by dividing their annual salary by 12. The result is rounded to two decimal places and presented with the alias 'Monthly Salary'.
Here's a brief explanation of each part of SQLite code:
- SELECT clause:
- It selects the "first_name" and "last_name" columns from the "employees" table.
- Expression in SELECT clause:
- It calculates the monthly salary by dividing the "salary" column by 12.
- The ROUND function is used to round the result to two decimal places.
- Alias:
- The result of the expression is aliased as 'Monthly Salary'.
- FROM clause:
- Specifies the table from which to retrieve the data, in this case, the "employees" table.
Go to:
PREV : Write a query to select first 10 records from a table.
NEXT : Simple select statements
Practice SQLite Online
Model Database
Structure of 'hr' database :
Improve this sample solution and post your code through Disqus.
What is the difficulty level of this exercise?