LeetCode SQL - Swap

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
11
12
13
14
15
/* Exchange the records

- seat PK: id

- SOLUTION: CASE
*/
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');