SQL Exercises: Salary exceeds 50% of their departments total salary
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
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);
first_name last_name Kimberely Grant Jennifer Whalen Michael Hartstein Susan Mavris Hermann Baer Shelley Higgins
The said query in SQL that retrieves the first name and last name of employees whose salary is greater than 50% of the total sum of salaries in their department.
The WHERE clause specifies the condition that filters the rows returned by the query. In this case, the salary of the employee must be greater than 50% of the sum of all salaries in the same department.
Using Subquery with IN:
SELECT e1.first_name, e1.last_name FROM employees e1 WHERE salary > 0.5 * ( SELECT SUM(salary) FROM employees e2 WHERE e1.department_id = e2.department_id GROUP BY department_id );
Using Subquery with JOIN:
SELECT e1.first_name, e1.last_name FROM employees e1 JOIN ( SELECT department_id, SUM(salary) as total_salary FROM employees GROUP BY department_id ) e2 ON e1.department_id = e2.department_id WHERE e1.salary > e2.total_salary * 0.5;
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Manager who works for a department based in the US.
Next SQL Exercise: Find out the details of employees who are managers.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to combine date from one field with time from another field - MS SQL Server
You can simply add the two.
- if the Time part of your Date column is always zero
- and the Date part of your Time column is also always zero (base date: January 1, 1900)
Adding them returns the correct result-
SELECT Combined = MyDate + MyTime FROM MyTable
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook