Employee Bonus - Problem

Given two tables: Employee and Bonus, write a SQL solution to report the name and bonus amount of each employee who satisfies either of the following conditions:

  • The employee has a bonus less than 1000
  • The employee did not get any bonus

Return the result table in any order.

Employee Table Schema:

The Employee table contains employee information including their ID, name, supervisor, and salary.

Bonus Table Schema:

The Bonus table contains the bonus amount for some employees. Not all employees may have an entry in this table.

Table Schema

Employee
Column Name Type Description
empId PK int Unique employee ID
name varchar Employee name
supervisor int Supervisor employee ID
salary int Employee salary
Primary Key: empId
Bonus
Column Name Type Description
empId PK int Employee ID (foreign key)
bonus int Bonus amount
Primary Key: empId

Input & Output

Example 1 — Mixed Bonus Scenarios
Input Tables:
Employee
empId name supervisor salary
3 Brad 4000
1 John 3 1000
2 Dan 3 2000
4 Thomas 3 4000
Bonus
empId bonus
2 500
4 2000
Output:
name bonus
John
Dan 500
Brad
💡 Note:

Brad and John have no bonus records (NULL), so they qualify. Dan has bonus 500 < 1000, so he qualifies. Thomas has bonus 2000 ≥ 1000, so he's excluded.
Example 2 — All Employees No Bonus
Input Tables:
Employee
empId name supervisor salary
1 Alice 3000
2 Bob 1 2500
Bonus
empId bonus
Output:
name bonus
Alice
Bob
💡 Note:

No employees have bonus records, so all employees qualify with NULL bonus values.

Constraints

  • 1 ≤ empId ≤ 500
  • name consists of lowercase English letters
  • 1 ≤ salary ≤ 10000
  • 1 ≤ bonus ≤ 10000

Visualization

Tap to expand
Employee Bonus - SQL Solution INPUT TABLES Employee Table empId name supervisor 1 John 3 2 Dan 3 3 Brad null 4 Thomas 3 Bonus Table empId bonus 2 500 4 2000 Join on: empId LEFT JOIN ALGORITHM STEPS 1 LEFT JOIN Tables Keep all employees, match bonus if exists 2 Filter Results WHERE bonus < 1000 OR bonus IS NULL 3 Select Columns Return name and bonus 4 Return Results Any order acceptable SELECT e.name, b.bonus FROM Employee e LEFT JOIN Bonus b ON e.empId = b.empId WHERE bonus<1000 OR NULL FINAL RESULT Output Table name bonus John null Dan 500 Brad null Included: John, Brad: No bonus (NULL) Dan: bonus 500 < 1000 Excluded: Thomas: bonus 2000 >= 1000 3 rows returned Key Insight: LEFT JOIN preserves all employees even without matching bonus records. The WHERE clause filters for two conditions: bonus < 1000 (low bonus) OR bonus IS NULL (no bonus entry). This handles both employees with small bonuses AND those who never received any bonus at all. TutorialsPoint - Employee Bonus | Optimal Solution (LEFT JOIN with NULL handling)

Related Problems

Asked in
Facebook 15 Amazon 12 LinkedIn 8
25.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen