SQL Exercise: List 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. 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:

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 SQL Exercise: Sort on highest salary of employees except CLERK.
Next SQL Exercise: List employees along with details of their departments.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Count the number of occurrences of a string in a VARCHAR field?
SELECT title, description, ROUND ( ( LENGTH(description) - LENGTH( REPLACE ( description, "value", "") ) ) / LENGTH("value") ) AS count FROM <table>
Ref: https://bit.ly/3PvNdT4
- Exercises: Weekly Top 12 Most Popular Topics
- Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- JavaScript: HTML Form Validation
- SQL Exercises, Practice, Solution - SUBQUERIES
- C Programming Exercises, Practice, Solution : For Loop
- Python Exercises, Practice, Solution
- Python Data Type: List - Exercises, Practice, Solution
- C++ Basic: Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - exercises on Employee Database
- SQL Exercises, Practice, Solution - exercises on Movie Database
- SQL Exercises, Practice, Solution - exercises on Soccer Database
- C Programming Exercises, Practice, Solution : Recursion