LeetCode SQL - Swap

Posted on Edited on Symbols count in article: 1.3k Reading time ≈ 1 mins.

Swap

626. Exchange Seats

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP TABLE IF EXISTS seat;
CREATE TABLE
    If NOT EXISTS seat (id INT, student VARCHAR(255));
TRUNCATE TABLE seat;
INSERT INTO
    seat (id, student)
VALUES
    ('1', 'Abbot');
INSERT INTO
    seat (id, student)
VALUES
    ('2', 'Doris');
INSERT INTO
    seat (id, student)
VALUES
    ('3', 'Emerson');
INSERT INTO
    seat (id, student)
VALUES
    ('4', 'Green');
INSERT INTO
    seat (id, student)
VALUES
    ('5', 'Jeames');
1
2
3
4
5
6
7
8
9
10
-- O(logn), O(n)
SELECT
    CASE
        WHEN id = (SELECT MAX(id) FROM Seat) AND id % 2 = 1 THEN id
        WHEN id % 2 = 0 THEN id - 1
        ELSE id + 1
    END AS id,
    student
FROM Seat
ORDER BY id asc;

627. Swap Salary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE IF EXISTS Salary;
CREATE TABLE
    if NOT EXISTS Salary (id INT, name VARCHAR(100), sex CHAR(1), salary INT);
TRUNCATE TABLE Salary;
INSERT INTO
    Salary (id, name, sex, salary)
VALUES
    ('1', 'A', 'm', '2500');
INSERT INTO
    Salary (id, name, sex, salary)
VALUES
    ('2', 'B', 'f', '1500');
INSERT INTO
    Salary (id, name, sex, salary)
VALUES
    ('3', 'C', 'm', '5500');
INSERT INTO
    Salary (id, name, sex, salary)
VALUES
    ('4', 'D', 'f', '500');
1
2
3
4
5
6
7
8
/*Swap Salary
- Salary PK: id

- SOLUTION: Update
 */
UPDATE Salary
SET
    sex = if (sex = 'f', 'm', 'f');