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:
If the number of students is odd, there is no need to change the last one’s seat.
Difficulty Level – Medium
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.