Exchange Seats

medium database sql

Problem

Seat(id, student). Swap names in adjacent seat pairs (1↔2, 3↔4, …). The last odd seat stays.

Inputs=[(1,'A'),(2,'D'),(3,'E'),(4,'C'),(5,'B')]

Output[(1,'D'),(2,'A'),(3,'C'),(4,'E'),(5,'B')]

Pairs (1,2) and (3,4) swapped; 5 unchanged.

rows (compact)

SELECT (CASE WHEN MOD(id,2)=1 AND id = (SELECT MAX(id) FROM Seat) THEN id
             WHEN MOD(id,2)=1 THEN id+1
             ELSE id-1 END) AS id, student
FROM Seat
ORDER BY id;

Explanation

We want to swap the names sitting in seats 1 and 2, then 3 and 4, and so on. Instead of moving any data around, we cleverly relabel the seat ids so that the right student ends up under each id when we sort.

The query uses a CASE on each row to decide its new id. If the id is even (the ELSE branch), it pairs with the seat just before it, so we output id-1. If the id is odd, it normally pairs with the seat after it, so we output id+1.

There is one special case: the very last seat when the total count is odd. That student has no partner, so the first branch checks MOD(id,2)=1 AND id = (SELECT MAX(id) FROM Seat) and keeps the id unchanged.

Because we only change the id labels (the student name stays glued to its original row), the final ORDER BY id pulls each student into the swapped position.

Example: seat 1 becomes id 2 and seat 2 becomes id 1, so when sorted, student D (originally seat 2) appears first. Seat 5 is the odd last seat, so it stays at id 5.

Time: O(n) Space: O(n)