w3resource
SQL exercises

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

Write a query to display the employee number, name( first name and last 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.

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

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: Write a query to display the employee ID and name ( first name and last name ) of all employees who works in such a department where the employees are working whose name containing a letter 'T'.
Next: Display the employee name( first name and last name ), employee id, and job title for all employees whose department location is Toronto.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming