SQLite Exercise: Get the names, salary, PF of all the employees
Write a query to get the names (first_name, last_name), salary, PF of all the employees (PF is calculated as 15% of 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 | | 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 "salary" columns
-- Calculating the 15% of the "salary" and aliasing it as "PF" (Provident Fund)
SELECT first_name, last_name, salary, salary * 0.15 PF
-- Specifying the table from which to retrieve the data, in this case, "employees"
FROM employees;
Output:
first_name last_name salary PF ---------- ---------- ---------- ---------- Steven King 24000 3600.0 Neena Kochhar 17000 2550.0 Lex De Haan 17000 2550.0 Alexander Hunold 9000 1350.0 Bruce Ernst 6000 900.0 .... ... ... ... Pat Fay 6000 900.0 Susan Mavris 6500 975.0 Hermann Baer 10000 1500.0 Shelley Higgins 12000 1800.0 William Gietz 8300 1245.0
Explanation:
The above SQLite query retrieves the "first_name," "last_name," and "salary" columns from the "employees" table. It also calculates 15% of the salary for each employee, naming the result as "PF" (Provident Fund).
Go to:
PREV : Write a query to get all employee details from the employee table order by first name, descending.
NEXT : Write a query to get the employee ID, names (first_name, last_name), salary in ascending order of salary.
Practice SQLite Online
Model Database
Structure of 'hr' database :
Improve this sample solution and post your code through Disqus.