﻿ SQL: Sort on highest salary of employees except CLERK

SQL Exercise: Sort on highest salary of employees except CLERK

SQL employee Database: Exercise-85 with Solution

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

85. From the following table, write a SQL query to select all employees except CLERK and sort the results in descending order by salary. Return employee name, job name, salary, grade and department name.

Pictorial Presentation:

Sample table: employees

Sample table: department

Sample Solution:

``````SELECT e.emp_name,
e.job_name,
e.salary,
d.dep_name
FROM employees e,
department d,
WHERE e.dep_id = d.dep_id
AND e.salary BETWEEN s.min_sal AND s.max_sal
AND e.job_name NOT IN('CLERK')
ORDER BY e.salary DESC;
``````

Sample Output:

``` emp_name | job_name  | salary  | grade | dep_name
----------+-----------+---------+-------+-----------
KAYLING  | PRESIDENT | 6000.00 |     5 | FINANCE
FRANK    | ANALYST   | 3100.00 |     4 | AUDIT
SCARLET  | ANALYST   | 3100.00 |     4 | AUDIT
JONAS    | MANAGER   | 2957.00 |     4 | AUDIT
BLAZE    | MANAGER   | 2750.00 |     4 | MARKETING
CLARE    | MANAGER   | 2550.00 |     4 | FINANCE
ADELYN   | SALESMAN  | 1700.00 |     3 | MARKETING
TUCKER   | SALESMAN  | 1600.00 |     3 | MARKETING
WADE     | SALESMAN  | 1350.00 |     2 | MARKETING
MADDEN   | SALESMAN  | 1350.00 |     2 | MARKETING
(10 rows)
```

Explanation:

The said query SQL that selects employee information including their name, job title, salary, department, and salary grade from 'employees', 'department', and 'salary_grade' tables. The query excludes any employees with a job title of "CLERK" and sorts the results in descending order based on salary.

The WHERE clause makes an implicit join that the employee's department ID must match the department ID in the department table and the employee's salary must fall within the min_salary and max_salary range for their salary grade and excludes those employees that job title must not be "CLERK"

The ORDER BY clause sorts the results in descending order based on salary.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: Employees according to the department in ASC order.
Next SQL Exercise: List employees working for department 1001 or 2001.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿