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

# 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## 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