SQL exercises on employee Database: List the employee ID, name, salary, department, grade, experience, and annual salary of employees working for department 1001 or 2001

SQL employee Database: Exercise-86 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

86. Write a query in SQL to list the employee ID, name, salary, department, grade, experience, and annual salary of employees working for department 1001 or 2001.

Sample table: employees

Sample table: department

Sample table: salary_grade

Sample Solution:

SELECT e.emp_id,
       e.emp_name,
       e.salary,
       s.grade,
       d.dep_name,
       age(CURRENT_DATE, hire_date) AS "Experience",
       12 * e.salary "Annual Salary"
FROM employees e,
     department d,
     salary_grade s
WHERE e.dep_id IN (1001,
                   2001)
  AND e.dep_id = d.dep_id
  AND e.salary BETWEEN s.min_sal AND s.max_sal ;

Sample Output:

 emp_id | emp_name | salary  | grade | dep_name |       Experience        | Annual Salary
--------+----------+---------+-------+----------+-------------------------+---------------
  68736 | ADNRES   | 1200.00 |     1 | AUDIT    | 20 years 8 mons 9 days  |      14400.00
  63679 | SANDRINE |  900.00 |     1 | AUDIT    | 27 years 1 mon 14 days  |      10800.00
  69324 | MARKER   | 1400.00 |     2 | FINANCE  | 26 years 9 days         |      16800.00
  67832 | CLARE    | 2550.00 |     4 | FINANCE  | 26 years 7 mons 22 days |      30600.00
  69062 | FRANK    | 3100.00 |     4 | AUDIT    | 26 years 1 mon 29 days  |      37200.00
  67858 | SCARLET  | 3100.00 |     4 | AUDIT    | 20 years 9 mons 12 days |      37200.00
  65646 | JONAS    | 2957.00 |     4 | AUDIT    | 26 years 9 mons 29 days |      35484.00
  68319 | KAYLING  | 6000.00 |     5 | FINANCE  | 26 years 2 mons 13 days |      72000.00
(8 rows)

Practice Online

Sample Database: employee

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

Previous: Write a query in SQL to list the name, job name, salary, grade and department name of employees except CLERK and sort result set on the basis of highest salary.
Next: Write a query in SQL to list the details of the employees along with the details of their departments.