w3resource

SQL Subquery Exercises: Identify all the employees who earn more than the average and who work in any of the IT departments

SQL SUBQUERY: Exercise-28 with Solution

From the following table, write a SQL query to find out which employees are earning more than the average salary and who work in any of the IT departments. Return last name.

Sample table: employees


Sample table: departments


Sample Solution:

SELECT last_name 
FROM employees 
WHERE department_id IN 
(SELECT department_id 
FROM departments 
WHERE department_name LIKE 'IT%') 
AND salary > 
(SELECT avg(salary) 
FROM employees);

Sample Output:

last_name
Hunold

Pictorial Presentation:

SQL Subqueries: Identify all the employees who earn more than the average and who work in any of the IT departments.

Practice Online


Query Visualization:

Duration:

Query visualization of Identify all the employees who earn more than the average and who work in any of the IT departments - Duration

Rows:

Query visualization of Identify all the employees who earn more than the average and who work in any of the IT departments - Rows

Cost:

Query visualization of Identify all the employees who earn more than the average and who work in any of the IT departments - Cost

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

Previous: From the following tables, write a SQL query to find those employees who work in departments located at ‘United Kingdom’. Return first name.
Next: From the following table, write a SQL query to find all those employees who earn more than an employee whose last name is ‘Ozer’. Sort the result in ascending order by last name. Return first name, last name and salary.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to count occurrences of a column value in SQL?

Input table:

id | age
--------
0  | 25
1  | 25
2  | 23
SELECT age, count(age) 
  FROM Students 
 GROUP by age

Output:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

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