﻿ SQL Challenges-1: Find most expensive and cheapest room from the hotels - w3resource

# SQL Challenges-1: Find most expensive and cheapest room from the hotels

## SQL Challenges-1: Exercise-58 with Solution

From the following table write a SQL query to find the cheapest and most expensive room in the hotels of a city. Return hotel ID, most expensive and cheapest room's ID.

Input:

Table: hotels

Structure:

FieldTypeNullKeyDefaultExtra
hotel_idintYES
floor_idintYES
room_nointYES
room_typevarchar(2)YES
price_weekdaysintYES
price_holidaysintYES

Data:

hotel_idfloor_idroom_noroom_typeprice_weekdays price_holidays
20112005D55006000
20112007Q75009000
20123008D53005000
20155005D62006000
20155010Q1000015000
20324051S42003800
20324056D48005000
20345058D57006300
20345065S80006500
20413825S40003800
20413830D39004500
20424620D65007000
20424625Q800010000
20424630S95008500
20424635Q1500020000
20525525S48004300
20535652Q72009000
20535658D52005000
20556010D65006300
20535670S55005000
20556015Q1850016300

Sample Solution:

SQL Code(MySQL):

``````create table hotels (
hotel_id integer(4),
floor_id integer(2),
room_no integer(4),
room_type varchar(2),
price_weekdays integer(5),
price_holidays integer(5));

insert into hotels values (201,	1,	2005,	'D',	5500,	6000 );
insert into hotels values (201,	1,	2007,	'Q',	7500,	9000 );
insert into hotels values (201,	2,	3008,	'D',	5300,	5000 );
insert into hotels values (201,	5,	5005,	'D',	6200,	6000 );
insert into hotels values (201,	5,	5010,	'Q',	10000,	15000 );
insert into hotels values (203,	2,	4051,	'S',	4200,	3800 );
insert into hotels values (203,	2,	4056,	'D',	4800,	5000 );
insert into hotels values (203,	4,	5058,	'D',	5700,	6300 );
insert into hotels values (203,	4,	5065,	'S',	8000,	6500 );
insert into hotels values (204,	1,	3825,	'S',	4000,	3800 );
insert into hotels values (204,	1,	3830,	'D',	3900,	4500 );
insert into hotels values (204,	2,	4620,	'D',	6500,	7000 );
insert into hotels values (204,	2,	4625,	'Q',	8000,	10000);
insert into hotels values (204,	2,	4630,	'S',	9500,	8500 );
insert into hotels values (204,	2,	4635,	'Q',	15000,	20000);
insert into hotels values (205,	2,	5525,	'S',	4800,	4300 );
insert into hotels values (205,	3,	5652,	'Q',	7200,	9000 );
insert into hotels values (205,	3,	5658,	'D',	5200,	5000 );
insert into hotels values (205,	5,	6010,	'D',	6500,	6300 );
insert into hotels values (205,	3,	5670,	'S',	5500,	5000 );
insert into hotels values (205,	5,	6015,	'Q',	18500,	16300 );

SELECT hotel, exp_room.room_no most_expensive_room_no, che_room.room_no cheapest_room_no
FROM hotels exp_room
JOIN (
SELECT hotel_id hotel, MAX(price_weekdays) costly, MIN(price_weekdays) cheapest
FROM hotels
GROUP BY hotel_id) t
ON exp_room.price_weekdays = t.costly
AND exp_room.hotel_id = t.hotel
JOIN hotels che_room
ON che_room.price_weekdays = t.cheapest
AND che_room.hotel_id = t.hotel
ORDER BY hotel;
```
```

Sample Output:

```hotel|most_expensive_room_no|cheapest_room_no|
-----+----------------------+----------------+
201|                  5010|            3008|
203|                  5065|            4051|
204|                  4635|            3830|
205|                  6015|            5525|
```

SQL Code Editor: