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 |
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.
Comments
Post a Comment