SQL Exercise: List the result in descending order of salary

SQL subqueries on employee Database: Exercise-11 with Solution

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

11. From the following table, write a SQL query to find those employees whose salary is the same as the salary of FRANK or SANDRINE. Sort the result-set in descending order by salary. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE salary IN
    (SELECT salary
     FROM employees e
     WHERE e.emp_name IN ('FRANK',
                          'BLAZE')
       AND employees.emp_id <> e.emp_id);

OR

SELECT *
FROM employees 
WHERE salary IN
    (SELECT salary
     FROM employees e
     WHERE (emp_name = 'FRANK'
            OR emp_name = 'BLAZE')
AND employees.emp_id <> e.emp_id)
ORDER BY salary DESC;

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |            |   2001
(1 row)

Explanation:

The given query in SQL that selects all employees whose salary is equal to the salary of either 'FRANK' or 'BLAZE', but not including their own salary from the 'employees' table.

The WHERE clause in the main query, which filters the results to only include rows where the salary is found in the salaries of either 'FRANK' or 'BLAZE', but not including their own salary determined by the subquery. The subquery, which selects the salaries of the employees named 'FRANK' and 'BLAZE' from the 'employees' table and excludes the salary of the current employee.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Personnel with department ID 2001 and ID 1001.
Next SQL Exercise: Designation or salaries exceed match Marker or Adelyn's.