SQL Exercise: Display the list in ascending order of location

SQL subqueries on employee Database: Exercise-3 with Solution

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

3. From the following table, write a SQL query to find those employees who work as 'MANAGERS' and 'ANALYST' and working in ‘SYDNEY’ or ‘PERTH’ with an experience more than 5 years without receiving the commission. Sort the result-set in ascending order by department location. Return employee ID, employee name, salary, and department name.

Sample table: employees

Sample table: department

Sample Solution:

-- Selecting employee details from specific departments and locations using implicit join
SELECT e.emp_id,
       e.emp_name,
       e.salary,
       d.dep_name
FROM employees e,
     department d
-- Cartesian join of employees and department tables using a comma in the FROM clause

WHERE d.dep_location IN ('SYDNEY',
                         'PERTH')
-- Filtering by department locations Sydney and Perth

  AND e.dep_id = d.dep_id
-- Joining employees and department tables based on department ID

  AND e.emp_id IN
    (SELECT e.emp_id
     FROM employees e
     WHERE e.job_name IN ('MANAGER',
                          'ANALYST')
       AND (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', e.hire_date)) > 5
       AND e.commission IS NULL)
-- Filtering employees based on specific job titles, experience, and no commission in the subquery

ORDER BY d.dep_location ASC;
-- Ordering the result set by department location in ascending order

Sample Output:

 emp_id | emp_name | salary  | dep_name
--------+----------+---------+-----------
  66928 | BLAZE    | 2750.00 | MARKETING
  67832 | CLARE    | 2550.00 | FINANCE
(2 rows)

Explanation:

The given query in SQL that selects the employee ID, name, salary, and department name of employees from the 'employees' and the 'department' table who work in departments located in Sydney or Perth, have been employed for more than 5 years, are not receiving commissions, and have a job title of either "MANAGER" or "ANALYST".

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

The WHERE clause then filters the result set that meet the following conditions:

The department location is either Sydney or Perth. The employee's job name is either "MANAGER" or "ANALYST".

The employee has been working for more than 5 years .

The employee does not receive any commission.

The results are sorted in ascending order based on the department location.

Alternative Solutions:

Using JOIN Syntax:

-- Selecting employee details from specific departments and locations
SELECT e.emp_id,
       e.emp_name,
       e.salary,
       d.dep_name
FROM employees e
JOIN department d ON e.dep_id = d.dep_id
-- Joining the employees and department tables based on department ID

WHERE d.dep_location IN ('SYDNEY', 'PERTH')
-- Filtering by department locations Sydney and Perth

  AND e.emp_id IN
    (
        -- Subquery filters employees based on job, experience, and no commission
        SELECT e.emp_id
        FROM employees e
        WHERE e.job_name IN ('MANAGER', 'ANALYST')
          AND (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', e.hire_date)) > 5
          AND e.commission IS NULL
    )
-- Filtering employees based on specific job titles, experience, and no commission in the subquery

ORDER BY d.dep_location ASC;
-- Ordering the result set by department location in ascending order

Explanation:

This solution uses the JOIN syntax to join the "employees" and "department" tables based on the dep_id. The WHERE clause filters employees from specific locations and departments and also includes a subquery for additional conditions.

Using INNER JOIN and Subquery:

-- Selecting employee details with INNER JOIN and subquery
-- Selects specific columns from employees and department tables with certain conditions

SELECT e.emp_id,
       e.emp_name,
       e.salary,
       d.dep_name
FROM employees e
INNER JOIN department d ON e.dep_id = d.dep_id
-- Joins the employees and department tables based on the department ID

WHERE d.dep_location IN ('SYDNEY', 'PERTH')
-- Filters by department locations Sydney and Perth

  AND e.emp_id IN
    (
        -- Subquery filters employees based on job, experience, and no commission
        SELECT emp_id
        FROM employees
        WHERE job_name IN ('MANAGER', 'ANALYST')
          AND (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', hire_date)) > 5
          AND commission IS NULL
    )
-- Filters employees based on specific job titles, experience, and no commission in the subquery

ORDER BY d.dep_location ASC;
-- Orders the result set by department location in ascending order

Explanation:

This solution uses the JOIN syntax to join the "employees" and "department" tables based on the dep_id. The WHERE clause filters employees from specific locations and departments and also includes a subquery for additional conditions.

Using EXISTS Subquery:

-- Selecting employee details using EXISTS subquery
SELECT e.emp_id,
       e.emp_name,
       e.salary,
       d.dep_name
FROM employees e
JOIN department d ON e.dep_id = d.dep_id
WHERE d.dep_location IN ('SYDNEY', 'PERTH')
  AND EXISTS
    (
        -- Subquery checks if there is at least one record
        SELECT 1
        FROM employees
        WHERE emp_id = e.emp_id
          AND job_name IN ('MANAGER', 'ANALYST')
          AND (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', hire_date)) > 5
          AND commission IS NULL
    )
ORDER BY d.dep_location ASC;

Explanation:

This solution uses the EXISTS subquery to check if there is at least one record meeting the specified conditions, simplifying the structure of the main query.

Practice Online

Structure of employee Database

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

Previous SQL Exercise: Display details and experience of all the managers.
Next SQL Exercise: ASC order of department ID and DESC order of job name.