Employee Bonus SQL Challenge - PostgreSQL Solution

1. Introduction

This blog post provides a PostgreSQL solution to report the name and bonus amount of each employee who received a bonus of less than 1000. This is a common scenario in HR and payroll analysis.

Problem

The task is to create a PostgreSQL query for the Employee and Bonus tables to return the names and bonuses of employees whose 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. PostgreSQL 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. This ensures the inclusion of all employees, even those without a bonus.

3. The WHERE clause filters out employees with bonuses of 1000 or more.

4. It provides a list of employees with bonuses under 1000, as well as those without any bonus.