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.

Problem

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;

Output:

name	bonus
Brad	null
John	null
Dan	500

Explanation:

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.