SQL Exercise: List employees working for department 1001 or 2001

Last update on December 20 2024 12:00:07 (UTC/GMT +8 hours)

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

86. From the following table, write a SQL query to find those employees who work in the department 1001 or 2001. Return employee ID, name, salary, department, grade, experience, and annual salary.

Pictorial Presentation:

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

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)

Explanation:

The said query in QL query that selects the emp_id, emp_name, salary, grade, dep_name, Experience, and Annual Salary of employees from 'employees', 'department', and 'salary_grade' tables who work in departments with IDs 1001 or 2001, and whose salaries fall within the range defined by the minimum and maximum salary grades.

The age() function is used to calculate the number of years between the current date and the hire_date column in the employees table.

The "Annual Salary" column is calculated by multiplying the salary column by 12 .

The IN operator is selects employees whose department IDs are either 1001 or 2001. The AND operator joins the three tables based on the dep_id column in the employees and department tables, and the min_sal and max_sal columns in the salary_grade table. The BETWEEN operator selects employees whose salaries fall within the range defined by the minimum and maximum salary grades.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: Sort on highest salary of employees except CLERK.
Next SQL Exercise: List employees along with details of their departments.