Complex SQL Queries Interview Questions

 Write a SQL Query to exchange the adjacent seats of Employees;

CREATE TABLE SEATS (SEATNO NUMBER, EMPNAME VARCHAR2(20));

INSERT INTO SEATS VALUES(101, 'Ravikant');

INSERT INTO SEATS VALUES(102, 'Suryakant');

INSERT INTO SEATS VALUES(103, 'Pritesh');

INSERT INTO SEATS VALUES(104, 'Rajan');

INSERT INTO SEATS VALUES(105, 'Manu');

INSERT INTO SEATS VALUES(106, 'Akif');

INSERT INTO SEATS VALUES(107, 'Subho');

COMMIT;

Input

Output

Solution

WITH LAST_SEAT AS

(SELECT MAX(SEATNO) MXST FROM SEATS),

FINAL_CTE AS

(

SELECT

CASE

WHEN MOD(SEATNO,2)!=0 AND SEATNO=(SELECT MXST FROM LAST_SEAT) THEN SEATNO

WHEN MOD(SEATNO,2)=0 THEN SEATNO-1 ELSE SEATNO+1 END AS SEATNO,

EMPNAME

FROM SEATS

)

SELECT * FROM FINAL_CTE

ORDER BY SEATNO;