SQL Exercise: Designation is same as ADLYNE or salary more than WADE

Last update on December 20 2024 11:41:20 (UTC/GMT +8 hours)

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

62. From the following table, write a SQL query to find those employees whose designation is same as the designation of ADELYN or the salary is more than the salary of WADE. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE job_name =
    (SELECT job_name
     FROM employees
     WHERE emp_name = 'ADELYN')
  OR salary >
    (SELECT salary
     FROM employees
     WHERE emp_name = 'WADE');

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
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
(11 rows)

Explanation:

The said query in SQL that retrieves all employees from the 'employees' table where the employee's job name matches the job name of the employee with name 'ADELYN' or the employee's salary is greater than the salary of the employee with name 'WADE'.

The WHERE clause filters the results to include only those rows where the job name of the employee matches a subquery.

The subquery selects the job name of the employee with the name 'ADELYN'.

The OR clause further filters the results to include records where the salary of the employee is greater than a subquery.

The subquery selects the salary of the employee with the name 'WADE'.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Employees of grade 2 and 3 who belongs to a city.
Next SQL Exercise: Employees in department 1001 with salaries over ADELYN.