Employee Bonus
Preview mode. Log in to edit, run, submit, and save progress.
Description
You have two tables: Employee and Bonus. The Bonus table contains bonuses for some employees, but not all. Write a SQL query to report the name and bonus of each employee whose bonus is less than 1000. Employees with no bonus entry should also be included (treat their bonus as NULL). Return the result in any order.
Database Schema
Employee
| Column Name | Type | Description |
|---|---|---|
| empId | INT | Primary key |
| name | VARCHAR | Employee name |
| supervisor | INT | Supervisor's empId (nullable) |
| salary | INT | Employee salary |
Bonus
| Column Name | Type | Description |
|---|---|---|
| empId | INT | FK to Employee, primary key |
| bonus | INT | Bonus amount |
Example
Employee
| empId | name | supervisor | salary |
|---|---|---|---|
| 3 | Brad | NULL | 4000 |
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 4 | Thomas | 3 | 4000 |
Bonus
| empId | bonus |
|---|---|
| 2 | 500 |
| 4 | 2000 |
Output
| name | bonus |
|---|---|
| Brad | NULL |
| John | NULL |
| Dan | 500 |
Explanation:
Brad and John have no bonus entry (NULL < 1000 counts). Dan has bonus 500 < 1000. Thomas has bonus 2000 which is not less than 1000.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Employee
| empId | name | supervisor | salary |
|---|---|---|---|
| 3 | Brad | NULL | 4000 |
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 4 | Thomas | 3 | 4000 |
Bonus
| empId | bonus |
|---|---|
| 2 | 500 |
| 4 | 2000 |
Output
| name | bonus |
|---|---|
| Brad | NULL |
| John | NULL |
| Dan | 500 |