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
121		Adam		8200.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:

SQL Subqueries: 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.

Practice Online


Query Visualization:

Duration:

Query visualization of 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 - Duration

Rows:

Query visualization of 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 - Rows

Cost:

Query visualization of 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 - Cost

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains a letter 'T'. Return employee ID, first name and last name.
Next: From the following table, write a SQL query to find those employees whose department located at ‘Toronto’. Return first name, last name, employee ID, job ID.

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