Exchange Seats
Task: 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.
Table: Seat
+-------------+--------------+
| Column Name | Type |
+-------------+--------------+
| id | INT |
| student | varchar(255) |
+-------------+--------------+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 |
+----+---------+View Reference Solution & Explanation ▼
This solution uses a `CASE` statement for conditional logic, aggregate functions to compute a single value from the data, and a subquery as a derived table or inline value.
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;