LeetCode 627 - Swap Sex of Employees

The problem is asking us to swap the sex values of all employees in a Salary table. Each row in the table represents an employee with four columns: id, name, sex, and salary. The sex column is an ENUM with values 'm' for male and 'f' for female.

leetcodeeasydatabase

LeetCode Problem 627

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

The problem is asking us to swap the sex values of all employees in a Salary table. Each row in the table represents an employee with four columns: id, name, sex, and salary. The sex column is an ENUM with values 'm' for male and 'f' for female. The task is to flip these values across all rows, so 'm' becomes 'f' and 'f' becomes 'm'.

The input is the current state of the Salary table, while the expected output is the updated table where all sex values have been swapped. The problem requires using a single SQL update statement, meaning we cannot use temporary tables, select statements for intermediate steps, or multiple updates.

Key points to note are that every employee has a valid sex value of either 'm' or 'f', and the table can contain any number of employees. Edge cases include tables with only 'm' or only 'f' values, or an empty table. Since the table has an ENUM type for sex, care must be taken to swap values without violating type constraints.

Approaches

A naive approach might try to select all rows, apply conditional logic in an external program or multiple SQL updates, and then write the values back. For instance, one could first set all 'm' to 'f' and then 'f' to 'm'. This approach is unnecessarily complex, requires multiple passes, and violates the constraint of a single update statement.

The key insight is that SQL allows conditional expressions directly in an update statement. By using a CASE WHEN expression, we can evaluate each row's current sex and assign the opposite value in a single atomic operation. This guarantees correctness because each row is updated independently and simultaneously in one pass.

Approach Time Complexity Space Complexity Notes
Brute Force O(n) O(n) Requires multiple updates or external computation; violates single-statement constraint
Optimal O(n) O(1) Single update statement using CASE WHEN; efficient and atomic

Algorithm Walkthrough

  1. Start with the Salary table containing all employee records. Each record has columns id, name, sex, and salary.
  2. Construct a single UPDATE SQL statement that operates on the Salary table. The update will modify the sex column.
  3. Use a CASE WHEN conditional expression to check the current value of sex. If it is 'm', assign 'f'. If it is 'f', assign 'm'. This ensures each value is flipped correctly.
  4. Apply the update to the entire table without any WHERE clause because all rows need modification.
  5. Execute the statement. SQL will handle each row independently, updating all rows in a single atomic operation.

Why it works: Each row is evaluated independently with the CASE WHEN expression. The update is atomic, so no intermediate state conflicts occur. The algorithm ensures that each 'm' is converted to 'f' and each 'f' to 'm' in one pass.

Python Solution

# Python solution is not directly applicable for SQL updates, but we can execute it using a cursor
import sqlite3

def swap_sex_of_employees(conn: sqlite3.Connection) -> None:
    """
    Swaps the sex values of all employees in the Salary table.
    Args:
    conn: sqlite3.Connection object connected to the database.
    """
    query = """
    UPDATE Salary
    SET sex = CASE
        WHEN sex = 'm' THEN 'f'
        WHEN sex = 'f' THEN 'm'
    END;
    """
    cursor = conn.cursor()
    cursor.execute(query)
    conn.commit()

In this Python implementation, we define a function that takes a database connection. We construct the SQL query with a CASE WHEN statement to swap the sex values and execute it through the cursor. Finally, we commit the transaction to save the changes.

Go Solution

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

func SwapSexOfEmployees(db *sql.DB) error {
    query := `
    UPDATE Salary
    SET sex = CASE
        WHEN sex = 'm' THEN 'f'
        WHEN sex = 'f' THEN 'm'
    END;
    `
    _, err := db.Exec(query)
    return err
}

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err := SwapSexOfEmployees(db); err != nil {
        log.Fatal(err)
    }
}

In Go, we use the database/sql package to connect and execute the query. The logic is identical to Python: the CASE WHEN expression handles the swap, and db.Exec executes the update. Go handles errors explicitly and requires connection management.

Worked Examples

Example 1:

Starting table:

id name sex salary
1 A m 2500
2 B f 1500
3 C m 5500
4 D f 500

Step-by-step application:

id name sex CASE WHEN logic new sex
1 A m sex = 'm' -> 'f' f
2 B f sex = 'f' -> 'm' m
3 C m sex = 'm' -> 'f' f
4 D f sex = 'f' -> 'm' m

Final table matches expected output:

id name sex salary
1 A f 2500
2 B m 1500
3 C f 5500
4 D m 500

Complexity Analysis

Measure Complexity Explanation
Time O(n) Each row is updated once; n is the number of rows in Salary
Space O(1) No additional storage is needed; update occurs in place

Since the update is in-place and uses a single pass through the table, the complexity is minimal and efficient.

Test Cases

# test cases

# Standard case
assert_swap_result([
    {"id":1,"name":"A","sex":"m","salary":2500},
    {"id":2,"name":"B","sex":"f","salary":1500}
], [
    {"id":1,"name":"A","sex":"f","salary":2500},
    {"id":2,"name":"B","sex":"m","salary":1500}
])

# All male
assert_swap_result([
    {"id":1,"name":"A","sex":"m","salary":1000},
    {"id":2,"name":"B","sex":"m","salary":2000}
], [
    {"id":1,"name":"A","sex":"f","salary":1000},
    {"id":2,"name":"B","sex":"f","salary":2000}
])

# All female
assert_swap_result([
    {"id":1,"name":"A","sex":"f","salary":1000}
], [
    {"id":1,"name":"A","sex":"m","salary":1000}
])

# Empty table
assert_swap_result([], [])

# Mixed salaries and ids
assert_swap_result([
    {"id":10,"name":"X","sex":"f","salary":3000},
    {"id":20,"name":"Y","sex":"m","salary":5000}
], [
    {"id":10,"name":"X","sex":"m","salary":3000},
    {"id":20,"name":"Y","sex":"f","salary":5000}
])
Test Why
Standard case Validates basic swap functionality
All male Edge case where no initial females exist
All female Edge case where no initial males exist
Empty table Edge case for zero rows
Mixed IDs and salaries Confirms algorithm works with arbitrary data

Edge Cases

One important edge case is an empty table. A naive implementation that assumes at least one row may fail if no rows exist. Our SQL UPDATE statement handles this naturally because updating zero rows is valid.

Another edge case is when all employees have the same sex. In such cases, the algorithm still works because the CASE WHEN evaluates each row independently and flips all values, even if there is only one category present.

A third edge case is invalid data in the sex column. While the problem guarantees valid ENUM values, in a real database, unexpected values could exist. Our CASE WHEN statement leaves any value other than 'm' or 'f' unchanged, which is safe and avoids runtime errors.

These edge cases demonstrate that the algorithm