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 table: salary_grade

Sample Solution:

SELECT e.emp_name,
       e.job_name,
       e.salary,
       s.grade,
       d.dep_name
FROM employees e,
     department d,
     salary_grade s
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.