Employee Bonus SQL Challenge - MySQL Solution

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

Bonus Table:

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.