Problem Description –
Mary is a teacher in a middle school and she has a table seat
storing students’ names and their corresponding seat ids.
The column id is a continuous increment.
Mary wants to change seats for the adjacent students.
Can you write a SQL query to output the result for Mary?

For the sample input, the output is:

Note:
If the number of students is odd, there is no need to change the last one’s seat.
Difficulty Level – Medium
Solution –
SELECT
CASE WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1 -- for odd ids
WHEN MOD(id, 2) != 0 AND counts = id THEN id -- special case for last seat
ELSE id - 1 -- For even ids
END as id,
student
FROM
seat, (SELECT COUNT(*) as counts
FROM seat) AS seat_count
ORDER by id
To solve this problem, first, we are calculating the number of seats using the COUNT() function. Then We are using 3 CASE statements, 2 for the odd ids and one for the even ids, two for odd ids as we have a special case. The first CASE statement says that when the id is odd MOD(id, 2) != 0
and counts != id
means when odd ids are other than 5 in this case 1 and 3 then add 1 to the ids. This will exchange the seat of a person with id 1 to 2 and 2 to 4. The second CASE statement is for the last odd row, which we do not need to change, so we kept the id as it is. And for the third CASE which is for the even rows, we subtracted the ids by 1, so the person with id 2 becomes 1 and the person with id 4 becomes 3.