SQL Challenges-1: Internal changes of beds

Last update on December 20 2024 11:58:46 (UTC/GMT +8 hours)

In a hostel, each room contains two beds. After every 6 months a student have to change their bed with his or her room-mate.
From the following tables write a SQL query to find the new beds of the students in the hostel. Return original_bed_id, student_name, bed_id and student_new.

Input:

Table: bed_info

Structure:

Field	Type	Null	Key	Default	Extra
bed_id	int(11)	YES
student_name	varchar(255)	YES

Data:

bed_id	student_name
101	Alex
102	Jhon
103	Pain
104	Danny
105	Paul
106	Rex
107	Philip
108	Josh
109	Evan
110	Green

Sample Solution:

SQL Code(MySQL):

CREATE TABLE bed_info(bed_id int, student_name varchar(255));
INSERT INTO bed_info VALUES (101, 'Alex');
INSERT INTO bed_info VALUES (102, 'Jhon');
INSERT INTO bed_info VALUES (103, 'Pain');
INSERT INTO bed_info VALUES (104, 'Danny');
INSERT INTO bed_info VALUES (105, 'Paul');
INSERT INTO bed_info VALUES (106, 'Rex');
INSERT INTO bed_info VALUES (107, 'Philip');
INSERT INTO bed_info VALUES (108, 'Josh');
INSERT INTO bed_info VALUES (109, 'Evan');
INSERT INTO bed_info VALUES (110, 'Green');


SELECT bed_id AS original_bed_id,student_name,
    (CASE
        WHEN MOD(bed_id, 2) != 0 AND counts != bed_id THEN bed_id + 1
        WHEN MOD(bed_id, 2) != 0 AND counts = bed_id THEN bed_id
        ELSE bed_id - 1
    END) AS bed_id,
    student_name AS student_new
FROM bed_info,
    (SELECT COUNT(*) AS counts
    FROM bed_info) AS bed_counts
ORDER BY bed_id ASC;

Sample Output:

original_bed_id|student_name|bed_id|student_new|
---------------|------------|------|-----------|
            102|Jhon        |   101|Jhon       |
            101|Alex        |   102|Alex       |
            104|Danny       |   103|Danny      |
            103|Pain        |   104|Pain       |
            106|Rex         |   105|Rex        |
            105|Paul        |   106|Paul       |
            108|Josh        |   107|Josh       |
            107|Philip      |   108|Philip     |
            110|Green       |   109|Green      |
            109|Evan        |   110|Evan       |

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Highest Single marks.
Next: Find the first login date for each customer.