﻿ SQL: Analysts or managers with a salary range, no commission

# SQL Exercise: Analysts or managers with a salary range, no commission

## SQL employee Database: Exercise-59 with Solution

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

59. From the following table, write a SQL query to search for employees who are working either as a MANAGER or an ANALYST with a salary between 2000 and 5000 (Begin and end values are included.) without commissions. Return complete information about the employees.

Sample table: employees

Pictorial Presentation:

Sample Solution:

SELECT *
FROM employees
WHERE job_name IN ('MANAGER',
'ANALYST')
AND salary BETWEEN 2000 AND 5000
AND commission IS NULL;

Sample Output:

emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
67832 | CLARE    | MANAGER  |      68319 | 1991-06-09 | 2550.00 |            |   1001
65646 | JONAS    | MANAGER  |      68319 | 1991-04-02 | 2957.00 |            |   2001
67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
(5 rows)

Explanation:

The given query in SQL that selects all columns from the 'employees' table where the job name is either 'MANAGER' or 'ANALYST', the salary is between 2000 and 5000, and the commission is NULL .

The "WHERE" clause restricts the results to employees whose job_name is either 'MANAGER' or 'ANALYST' using the "IN" operator to check if the job_name value matches either of the given values in the list and also filters the result set to employees whose salary falls within the range of 2000 and 5000 using the "BETWEEN" operator and it again used "IS NULL" operator that filters the results to employees whose commission is NULL.

## Practice Online

Sample Database: employee

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

Previous SQL Exercise: Salary of FRANK if his salary is equal to max_sal.
Next SQL Exercise: List the employees at a given place for over 10 years.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿