SQL Exercise: Employees with location, salary range, and joined in 91

SQL employee Database: Exercise-61 with Solution

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

61. From the following table, write a SQL query to find the employees who joined in 1991 and whose department location is SYDNEY or MELBOURNE with a salary range of 2000 to 5000 (Begin and end values are included.). Return employee ID, employee name, department ID, salary, and department location.

Pictorial Presentation:

Sample table: employees

Sample table: department

Sample Solution:

SELECT e.emp_id,
       e.emp_name,
       e.dep_id,
       e.salary,
       d.dep_location
FROM employees e,
     department d
WHERE e.dep_id = d.dep_id
  AND d.dep_location IN ('SYDNEY',
                         'MELBOURNE')
  AND to_char(e.hire_date,'YY') = '91'
  AND e.salary BETWEEN 2000 AND 5000;

Sample Output:

 emp_id | emp_name | dep_id | salary  | dep_location
--------+----------+--------+---------+--------------
  67832 | CLARE    |   1001 | 2550.00 | SYDNEY
  65646 | JONAS    |   2001 | 2957.00 | MELBOURNE
  69062 | FRANK    |   2001 | 3100.00 | MELBOURNE
(3 rows)

Explanation:

The given statement in SQL that selects the employee ID, name, department ID, salary, and department location of employees who work in the departments located in either 'SYDNEY' or 'MELBOURNE', whose hire date is in the year 1991, and whose salary falls within the range of 2000 and 5000.

The query joins the 'employees' and 'department' tables which returns rows that have a matching record in both tables based on the common column dep_id.

The "WHERE" clause filters the results using the "IN" operator to employees whose department location is either 'SYDNEY' or 'MELBOURNE' , it further filters the results to employees whose hire date is in the year 1991 by using the "to_char" function to convert the hire date to a string with a format of YY and comparing the result to the string '91'. It then filters the results to employees whose salary falls within the range of 2000 and 5000 using the "BETWEEN" operator.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: List the employees at a given place for over 10 years.
Next SQL Exercise: Employees location, grade, and experience over 25 years.