w3resource

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:

SQL exercises on employee Database: List the name, job name, salary, grade and department name of employees except CLERK and sort result set on the basis of highest salary

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)

Practice Online


Sample Database: employee

employee database structure

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.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert month number to month name function in SQL:

SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))

Ref: https://bit.ly/3DBcE38