1. Introduction
This blog post demonstrates how to use MySQL to report the name and bonus amount of each employee whose bonus is less than 1000. This type of query is commonly used in HR and payroll departments to analyze employee compensation.
Problem
The task is to create a MySQL query that joins two tables - Employee and Bonus - and returns the names and bonuses of employees where the bonus is less than 1000, including those with no bonus.
Database Table:
Employee Table:empId | name | supervisor | salary |
---|---|---|---|
1 | John | 3 | 1000 |
empId | bonus |
---|---|
2 | 500 |
3. MySQL Solution
SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b ON e.empId = b.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;
Output:
name | bonus |
---|---|
Brad | null |
John | null |
Dan | 500 |
Explanation:
1. The query joins the Employee table with the Bonus table using a LEFT JOIN on empId.
2. It ensures that all employees are included, even those without a bonus.
3. The WHERE clause filters the results to include only employees whose bonuses are less than 1000 or null (indicating no bonus).
4. This provides a complete list of employees with bonuses under 1000 or without any bonuses.
Comments
Post a Comment