SQL Exercise: Employees in department 1001 with salaries over ADELYN

SQL subqueries on employee Database: Exercise-63 with Solution

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

63. From the following table, write a SQL query to find those employees of department 1001 whose salary is more than the salary of ADELYN. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE dep_id = 1001
  AND salary >
    (SELECT salary
     FROM employees
     WHERE emp_name = 'ADELYN');

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
(2 rows)

Explanation:

The said query in SQL that retrieves all employees from the 'employees' table where the employee's department ID is 1001 and the employee's salary is greater than the salary of the employee with name 'ADELYN'.

The WHERE clause filters the results to include only those departments where the department ID of the employee is 1001.

The AND operator further filters the results to include records where the salary of the employee is greater than the salary obtained from a subquery.

This subquery selects the salary of the employee with the name 'ADELYN'.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Designation is same as ADLYNE or salary more than WADE.
Next SQL Exercise: Managers senior to KAYLING and junior to SANDRINE.