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.
Sample table: employees
Sample table: department
Sample table: salary_grade
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;
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)
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.
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.
SQL: Tips of the Day
How to select rows with no matching entry in another table?
SELECT t1.ID FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook