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:

Data:

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:

Contribute your code and comments through Disqus.

Previous: Find the employees in department Administration who solved the cases for all quarters are more than 1200.
Next: Managers who can ordered more than four employees.