﻿ SQL: Employees with common designation as SANDRINE or ADELYN

# 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

``````SELECT *
FROM employees
WHERE job_name IN
(SELECT job_name
FROM employees
WHERE emp_name IN('SANDRINE',
``````

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿