SQL Exercise: Employees, senior to BLAZE and work at PERTH, BRISBANE

SQL subqueries on employee Database: Exercise-14 with Solution

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

14. From the following table, write a SQL query to find those employees who are senior to BLAZE and working at PERTH or BRISBANE. Return complete information about the employees.

Sample table: employees

Sample table: department

Sample Solution:

SELECT *
FROM employees e,
     department d
WHERE d.dep_location IN ('PERTH',
                         'BRISBANE')
  AND e.dep_id = d.dep_id
  AND e.hire_date <
    (SELECT e.hire_date
     FROM employees e
     WHERE e.emp_name = 'BLAZE') ;

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id | dep_id | dep_name  | dep_location
--------+----------+----------+------------+------------+---------+------------+--------+--------+-----------+--------------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001 |   3001 | MARKETING | PERTH
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001 |   3001 | MARKETING | PERTH
(2 rows)

Explanation:

The given query in SQL that selects all employees who work in departments located in "PERTH" or "BRISBANE" and who were hired before the employee named "BLAZE" from the 'employees' and 'department' tables.

The WHERE clause filters the results to only include rows where the department location is either "PERTH" or "BRISBANE".

In the WHERE clause the query joins the 'employees' table and the 'department' table based on the "dep_id" column.

The subquery in the WHERE clause selects the hire date of the employee named "BLAZE". The main query then selects all employees whose hire date is earlier than this value.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Employees salary higher than total SALESMAN salaries.
Next SQL Exercise: List the result in the ascending order of experience.