﻿ SQL Challenges-1: Internal changes of beds - 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:

﻿

## 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