Employee Bonus SQL Challenge - Oracle Solution

1. Introduction

This blog post discusses how to retrieve employee names and their bonus amounts, specifically targeting those with a bonus of less than 1000, using Oracle SQL. This type of query is often used in financial reporting and payroll management.


The objective is to develop an Oracle SQL query that reports the name and bonus amount for each employee who has a bonus of less than 1000, including those employees who did not receive any bonus.

Database Table:

Employee Table:
empId name supervisor salary
1 John 3 1000
Bonus Table:
empId bonus
2 500

3. Oracle 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;


name bonus
Brad null
John null
Dan 500


1. The query uses a LEFT JOIN to combine the Employee and Bonus tables based on the empId.

2. This join ensures that all employees are included, even those without a bonus.

3. The WHERE clause is applied to filter employees with a bonus of less than 1000, as well as those who did not receive any bonus (indicated by bonus IS NULL).

4. The result is a list of employees with either no bonus or bonuses under 1000.