SQL Exercise: ASC order of department ID and DESC order of job name

SQL subqueries on employee Database: Exercise-4 with Solution

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

4. From the following tables, write a SQL query to find those employees work at SYDNEY or working in the FINANCE department with an annual salary above 28000, but the monthly salary should not be 3000 or 2800 and who do not work as a MANAGER and whose ID contain a digit of '3' or '7' in 3rd position. Sort the result-set in ascending order by department ID and descending order by job name. Return employee ID, employee name, salary, department name, department location, department ID, and job name.

Sample table: employees

Sample table: department

Sample Solution:

SELECT E.emp_id,
       E.emp_name,
       E.salary,
       D.dep_name,
       D.dep_location,
       E.dep_id,
       E.job_name
FROM employees E,
     department D
WHERE (D.dep_location = 'SYDNEY'
       OR D.dep_name = 'FINANCE')
  AND E.dep_id=D.dep_id
  AND E.emp_id IN
    (SELECT emp_id
     FROM employees E
     WHERE (12*E.salary) > 28000
       AND E.salary NOT IN (3000,
                            2800)
       AND E.job_name !='MANAGER'
       AND (trim(to_char(emp_id,'99999')) LIKE '__3%'
            OR trim(to_char(emp_id,'99999')) LIKE '__7%'))
ORDER BY E.dep_id ASC,
         E.job_name DESC;

Sample Output:

 emp_id | emp_name | salary  | dep_name | dep_location | dep_id | job_name
--------+----------+---------+----------+--------------+--------+-----------
  68319 | KAYLING  | 6000.00 | FINANCE  | SYDNEY       |   1001 | PRESIDENT
(1 row)

Explanation:

The given query in SQL that selects the employee ID, employee name, salary, department name, department location, and job name for employees from the 'employees' and 'department' tables who work in the location Sydney or the Finance department, whose salary multiplied by 12 is greater than 28,000, whose salary is not 3,000 or 2,800, whose job title is not "Manager", and whose employee ID ends with either 3 or 7.

The query joins the 'employees' and 'department' tables based on the department ID column.

Then it filters the result set by the filtering conditions to narrow down the results are as follows:

The department location is either 'SYDNEY' or the department name is 'FINANCE'.

The employee's salary, when multiplied by 12, is greater than 28000.

The employee's salary is not 3000 or 2800.

The employee's job_name is not 'MANAGER'.

The employee's emp_id ends with either '3' or '7'.

The results are sorted in ascending order by dep_id and descending order by job_name.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Display the list in ascending order of location.
Next SQL Exercise: List all the employees of grade 2 and 3.