SQL Exercise: Employees with common designation as SANDRINE or ADELYN

SQL subqueries on employee Database: Exercise-16 with Solution

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

16. From the following table, write a SQL query to find those employees whose designation is same as the designation of SANDRINE or ADELYN. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE job_name IN
    (SELECT job_name
     FROM employees
     WHERE emp_name = 'SANDRINE'
       OR emp_name = 'ADELYN');

OR

SELECT *
FROM employees
WHERE job_name IN
    (SELECT job_name
     FROM employees
     WHERE emp_name IN('SANDRINE',
                       'ADELYN'));

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  63679 | SANDRINE | CLERK    |      69062 | 1990-12-18 |  900.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
  68736 | ADNRES   | CLERK    |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
  69324 | MARKER   | CLERK    |      67832 | 1992-01-23 | 1400.00 |            |   1001
(8 rows)

Explanation:

The given query in SQL that returns all information about employees whose job name is the same as the job name of either 'SANDRINE' or 'ADELYN'.

The subquery in the WHERE clause that selects the job_name column from the employees table where the emp_name column is either 'SANDRINE' or 'ADELYN'.The WHERE clause then filters for the main query to return only the rows where the job_name is in that list.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List the result in the ascending order of experience.
Next SQL Exercise: List any job of department ID 1001 not in ID 2001.