Exchange Seats

Table: Seat

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+
id is the primary key (unique value) column for this table.
Each row of this table indicates the name and the ID of a student.
The ID sequence always starts from 1 and increments continuously.

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

The result format is in the following example.

Example 1:

**Input:** 
Seat table:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
**Output:** 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
**Explanation:** 
Note that if the number of students is odd, there is no need to change the last one's seat.

-- Approach: Use CASE statement to handle swapping of IDs
SELECT 
    CASE
        WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM Seat) THEN id
        WHEN id % 2 = 1 THEN id + 1
        ELSE id - 1
    END AS id,
    student
FROM Seat
ORDER BY id ASC;

Explanation:

1. CASE Statement:
   • We use a CASE statement to determine the new id for each student based on the following conditions:
2. Odd ID and Last Student:
   • WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM Seat) THEN id: This condition checks if the id is odd and if it's the maximum id in the table (i.e., the last student when the number of students is odd).
   • If both conditions are true, we keep the original id for the last student.
3. Odd ID:
   • WHEN id % 2 = 1 THEN id + 1: If the id is odd (but not the last student), we increment it by 1 to swap it with the next student.
4. Even ID:
   • ELSE id - 1: If the id is even, we decrement it by 1 to swap it with the previous student.
5. ORDER BY id ASC:
   • Finally, we order the result table by the new id in ascending order to meet the requirements of the problem.