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

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 tables, write a SQL query to find all employees except CLERK and sort the result-set 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)

Practice Online

Sample Database: employee

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 employees according to the department in ascending order. Return name, job name, department, salary, and grade.
Next: From the following table, write a SQL query to find those employees work in the department 1001 or 2001. Return employee ID, name, salary, department, grade, experience, and annual salary.