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.