w3resource

SQL Challenges-1: Internal changes of beds

SQL Challenges-1: Exercise-24 with Solution

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:

FieldTypeNullKeyDefaultExtra
bed_idint(11)YES
student_namevarchar(255)YES

Data:

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

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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8