﻿ SQL SUBQUERIES: Find those employees who earn more than the average salary and work in the same department as an employee whose first name contains the letter 'J' - w3resource

# SQL Subquery Exercises: Display the employee number, name, and salary for all employees who earn more than the average salary and who work in a department with any employee with a 'J' in their name

## SQL SUBQUERY: Exercise-15 with Solution

From the following tables, write a SQL query to find those employees who earn more than the average salary and work in the same department as an employee whose first name contains the letter 'J'. Return employee ID, first name and salary.

Sample table: employees

Sample Solution:

``````SELECT employee_id, first_name , salary
FROM employees
WHERE salary >
(SELECT AVG (salary)
FROM employees )
AND  department_id IN
( SELECT department_id
FROM employees
WHERE first_name LIKE '%J%');
``````

Sample Output:

```employee_id	first_name	salary
108		Nancy		12000.00
109		Daniel		9000.00
110		John		8200.00
111		Ismael		7700.00
112		Jose Manuel	7800.00
113		Luis		6900.00
120		Matthew		8000.00
122		Payam		7900.00
123		Shanta		6500.00
145		John		14000.00
146		Karen		13500.00
147		Alberto		12000.00
148		Gerald		11000.00
149		Eleni		10500.00
150		Peter		10000.00
151		David		9500.00
152		Peter		9000.00
153		Christopher	8000.00
154		Nanette		7500.00
155		Oliver		7000.00
156		Janette		10000.00
157		Patrick		9500.00
158		Allan		9000.00
159		Lindsey		8000.00
160		Louise		7500.00
161		Sarath		7000.00
162		Clara		10500.00
163		Danielle	9500.00
164		Mattea		7200.00
165		David		6800.00
168		Lisa		11500.00
169		Harrison	10000.00
170		Tayler		9600.00
171		William		7400.00
172		Elizabeth	7300.00
174		Ellen		11000.00
175		Alyssa		8800.00
176		Jonathon	8600.00
177		Jack		8400.00
```

Pictorial Presentation:

## Query Visualization:

Duration:

Rows:

Cost:

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?

﻿

## SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

Ref: https://bit.ly/3cayyhJ