Set each employee bonus to 10% of the department average salary.

Problem Statement

<p>Set each employee bonus to 10% of the department average salary.</p>

Examples

Input: employees table: +----+---------+------------+--------+-------+ | id | name | department | salary | bonus | +----+---------+------------+--------+-------+ | 1 | Alice | IT | 90000 | 0 | | 2 | Bob | IT | 80000 | 0 | | 3 | Charlie | HR | 60000 | 0 | +----+---------+------------+--------+-------+

Output: +----+---------+------------+--------+-------+ | id | name | department | salary | bonus | +----+---------+------------+--------+-------+ | 1 | Alice | IT | 90000 | 8500 | | 2 | Bob | IT | 80000 | 8500 | | 3 | Charlie | HR | 60000 | 6000 | +----+---------+------------+--------+-------+

Explanation: The query retrieves the requested records satisfying all problem requirements.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Identify the filtering conditions needed to isolate the target rows from the table. 💡 Hint 2: Use appropriate operators: = for exact matches, LIKE for wildcard patterns, IN for sets, or BETWEEN for numeric/date ranges. 💡 Hint 3: Combine filters with AND/OR if multiple conditions exist: SELECT <cols> FROM <table> WHERE <condition>;

Editorial & Approach

Problem Overview & Intuition

To solve "UPDATE with Subquery", we query the relational database engine using declarative SQL. The goal is to set each employee bonus to 10% of the department average salary. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply WHERE filtering to isolate the requested data.
Format & Order: Sort the resulting records according to specified order criteria.

Optimal Implementation (SQL)

UPDATE employees SET bonus = (SELECT AVG(salary) * 0.1 FROM employees e2 WHERE e2.department = employees.department); SELECT * FROM employees ORDER BY id;

Complexity Analysis

Time Complexity O(N log N) for sorting or partitioning rows.

Space Complexity O(N) for intermediate group hash tables or window buffers.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

UPDATE with Subquery

Medium

Set each employee bonus to 10% of the department average salary.

Example Scenarios

1Example 1

Input:

employees table

id	name	department	salary
1	Alice	IT	90000
2	Bob	IT	80000
3	Charlie	HR	60000

Output:

id	name	department	salary	bonus
1	Alice	IT	90000	8500
2	Bob	IT	80000	8500
3	Charlie	HR	60000	6000

Explanation:

The query retrieves the requested records satisfying all problem requirements.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.