Skip to content
>_sqlbuddy
mediumCASE WHENSubqueries

Exchange Seats

Swap the student in each adjacent pair of seats.

Start practice

Problem statement

Prompt

Given the seat table below, write a query that swaps the student of every adjacent pair of seats — seat 1 swaps with seat 2, seat 3 with seat 4, and so on.

  • If the last seat has an odd id (no partner), that student stays in place.
  • The result must be ordered by `id`.
  • Output columns: id and student.

Example

idstudent
1Abbot
2Doris
3Emerson
4Green

Expected result:

idstudent
1Doris
2Abbot
3Green
4Emerson

Constraints

  • seat.id is the primary key and is contiguous (no gaps).

Tips

  • CASE WHEN id % 2 = 1 THEN id + 1 ELSE id - 1 END gives the partner's id.
  • Use a LEFT JOIN or correlated subquery to fetch the partner's student; when the partner is missing (last odd seat), keep the original student.

Schema

CREATE TABLE seat (
    id      INTEGER PRIMARY KEY,
    student TEXT NOT NULL
);

Sample data

INSERT INTO seat (id, student) VALUES
  (1, 'Abbot'),
  (2, 'Doris'),
  (3, 'Emerson'),
  (4, 'Green'),
  (5, 'Jeames');

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: swap adjacent pairs; odd tail stays put.
SELECT s1.id,
       COALESCE(s2.student, s1.student) AS student
FROM seat s1
LEFT JOIN seat s2
  ON s2.id = CASE
                 WHEN s1.id % 2 = 1 THEN s1.id + 1
                 ELSE s1.id - 1
             END
ORDER BY s1.id;

Key concepts

  • CASE WHEN
  • Odd/even
  • Correlated subquery

Related questions

Learn more