w3resource

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:

SQL exercises on employee Database: List the employees along with their location who belongs to SYDNEY, MELBOURNE with a salary range between 2000 and 5000 and joined in 1991

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

employee database structure

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Concatenate strings of a string field in a PostgreSQL 'group by' query:

Input:

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave
SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Output:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

Database: PostgreSQL

Ref: https://bit.ly/2XTiRjq

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook