Swap Sex of Employees

Difficulty: Easy | Acceptance: 84.60% | Paid: No Topics: Database

Table: Employee

+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | sex | ENUM | | salary | int | +-------------+---------+ id is the primary key for this table. The sex column is ENUM (type) of value (‘m’, ‘f’). Each row of this table contains information about an employee.

Write an SQL query to swap all ‘f’ and ‘m’ values (i.e., change all ‘f’ values to ‘m’ and all ‘m’ values to ‘f’) with a single update statement. Note that you must write a single update statement, do not write any select statement for this problem.

• Examples
• Constraints
• Approach 1: Using CASE WHEN
• Approach 2: Using IF Function (MySQL)
• Approach 3: Using REPLACE Function

Examples

Example 1:

Input: Employee table: +----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 | +----+------+-----+--------+

Output: +----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500 | +----+------+-----+--------+

Explanation: (1, A) and (3, C) were changed from ‘m’ to ‘f’. (2, B) and (4, D) were changed from ‘f’ to ‘m’.

Constraints

The number of rows in the Employee table is between 1 and 100.
The salary values are between 1 and 1000000.

Approach 1: Using CASE WHEN

Intuition Use a CASE WHEN statement to conditionally update the sex column based on its current value.

Steps

• Write an UPDATE statement for the Employee table
• Use CASE WHEN to check if sex is ‘m’, then set it to ‘f’, otherwise set it to ‘m’

# SQL solution:
# UPDATE Employee
# SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END;

import pandas as pd

def swap_sex(employee: pd.DataFrame) -> pd.DataFrame:
    employee['sex'] = employee['sex'].apply(lambda x: 'f' if x == 'm' else 'm')
    return employee

Complexity

• Time: O(n) where n is the number of rows in the Employee table
• Space: O(1) additional space
• Notes: This is the most portable solution across different SQL databases

Approach 2: Using IF Function (MySQL)

Intuition Use MySQL’s IF function to perform a conditional swap in a single expression.

Steps

• Write an UPDATE statement for the Employee table
• Use IF function to check if sex is ‘m’, then return ‘f’, otherwise return ‘m’

# SQL solution:
# UPDATE Employee
# SET sex = IF(sex = 'm', 'f', 'm');

import pandas as pd

def swap_sex(employee: pd.DataFrame) -> pd.DataFrame:
    employee['sex'] = employee['sex'].apply(lambda x: 'f' if x == 'm' else 'm')
    return employee

Complexity

• Time: O(n) where n is the number of rows in the Employee table
• Space: O(1) additional space
• Notes: MySQL-specific syntax, more concise than CASE WHEN

Approach 3: Using REPLACE Function

Intuition Use the REPLACE function to swap ‘m’ with ‘f’ and ‘f’ with ‘m’ by using a temporary character.

Steps

• First replace ‘m’ with a temporary character (e.g., ‘x’)
• Then replace ‘f’ with ‘m’
• Finally replace the temporary character ‘x’ with ‘f’

# SQL solution:
# UPDATE Employee
# SET sex = REPLACE(REPLACE(REPLACE(sex, 'm', 'x'), 'f', 'm'), 'x', 'f');

import pandas as pd

def swap_sex(employee: pd.DataFrame) -> pd.DataFrame:
    employee['sex'] = employee['sex'].apply(lambda x: 'f' if x == 'm' else 'm')
    return employee

Complexity

• Time: O(n) where n is the number of rows in the Employee table
• Space: O(1) additional space
• Notes: Works in most SQL databases but requires a temporary character that doesn’t exist in the data