w3resource

SQL exercises on employee Database: List the employees who are working either MANAGER or ANALYST with a salary range between 2000 to 5000 without any 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 find those employees who are working either as a MANAGER or an ANALYST with a salary in the range 2000, 5000 (Begin and end values are included.) without any commission. Return complete information about the employees.

Sample table: employees


Pictorial Presentation:

SQL exercises on employee Database: List the employees who are working either MANAGER or ANALYST with a salary range between 2000 to 5000 without any commission

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)

Practice Online


Sample Database: employee

employee database structure

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

Previous: From the following table, write a SQL query to find the name and salary of the employee FRANK. Salary should be equal to the maximum salary within his or her salary group.
Next: From the following table, write a SQL query to find those employees working at PERTH, or MELBOURNE with an experience over 10 years. Return employee ID, employee name, department ID, salary, and department location.

What is the difficulty level of this exercise?