w3resource

SQL Subquery Exercises: Find out the names of all employees whose salary is greater than 50% of their department's total salary bill

SQL SUBQUERY: Exercise-31 with Solution

From the following tables, write a SQL query to find those employees whose salaries exceed 50% of their department's total salary bill. Return first name, last name.

Sample table: employees


Sample Solution:

SELECT e1.first_name, e1.last_name 
FROM employees e1 
WHERE salary > 
( SELECT (SUM(salary))*.5 
FROM employees e2 
WHERE e1.department_id=e2.department_id);

Sample Output:

first_name	last_name
Kimberely	Grant
Jennifer	Whalen
Michael		Hartstein
Susan		Mavris
Hermann		Baer
Shelley		Higgins

Pictorial Presentation:

SQL Subqueries: Find out the names of all employees whose salary is greater than 50% of their department's total salary bill.

Practice Online


Query Visualization:

Duration:

Query visualization of Find out the names of all employees whose salary is greater than 50% of their department’s total salary bill - Duration

Rows:

Query visualization of Find out the names of all employees whose salary is greater than 50% of their department’s total salary bill - Rows

Cost:

Query visualization of Find out the names of all employees whose salary is greater than 50% of their department’s total salary bill - 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 under a manager based in ‘US’. Return first name, last name.
Next: From the following tables, write a SQL query to find those employees who are managers. Return all the fields of employees table.

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