Is this a correct query.

Hotel

HotelNo, HotelName, City

Room

RoomNo, HotelNo, Type, Price

Booking

HotelNo, GuestNo, DateFrom, DateTo, RoomNo

Guest

GuestNo, GuestName, GuestAddress

Room Type are P, D , S

What is the most commonly booked room type for each hotel in London?

SELECT MAX (R.TYPE)
FROM ROOM R, HOTEL H, Booked B
WHERE H.CITY= ‘London’
AND B.DATEFROM < SYSDATE AND
B.DATETO > SYSDATE
GROUP BY R.TYPE

Please let me know, if this is correct.

Thanks

LVL 2
Apu_ShahAsked:
Who is Participating?
 
wstuphCommented:
What's the database?

SQL Server method that I'd use:

select top 1 r.type, count(*) as RoomCount
FROM ROOM R
inner join HOTEL H
on r.hotelno = h.hotelno
inner join Booked B
on r.roomno = b.roomno
WHERE H.CITY= ‘London’
AND B.DATEFROM < SYSDATE AND
B.DATETO > SYSDATE
GROUP BY R.TYPE
order by count(*) desc
0
 
cwileCommented:
If your above query doesn't work, try something like this.

SELECT Room.HotelNo, Max(Room.Type) AS MaxType, Hotel.HotelNo, Hotel.HotelCity, Booking.HotelNo, Booking.DateFrom, Booking.DateTo
FROM (Room INNER JOIN Hotel ON Room.HoteNo = Hotel.HotelNo) INNER JOIN Booking ON Room.HotelNo = Booking.HotelNo
GROUP BY MaxType, Hotel.HotelCity, Booking.DateFrom, Booking.DateTo
HAVING (((Booking.DateFrom) < SYSDATE) And ((Booking.DateTo) > SYSDATE) AND ((Hotel.HotelCity)="London"))


(not sure if my syntax is completely correct, just kinda did it on a whim :p)
0
 
mnrzCommented:
Hi
first of all I think the  conditions:  
    B.DateFrom < SYSDATE and
     B.DateTo > SYSDATE
are not necessary but, any way, as far sa I know, It's not possible to do this just by one select statement.
the following statement show you how many room booked in every hotel

Select H.Hotel_Name as name,
          R.Type as type,
          Count(R.Type) as cnt
From Room R, Hotel H,Booked B
Where
     H.Hotel_No = B.Hotel_No and
     H.Hotel_No = R.Hotel_No and
     R.Room_No = B.Room_No and
     H.City = "London" and
     B.DateFrom < SYSDATE and
     B.DateTo > SYSDATE
Group By name,type

I dont know which DataBase you are using but in Oracle it is possible to selecting from another select:

select name,type,max(cnt)
from (
Select H.Hotel_Name as name,
          R.Type as type,
          Count(R.Type) as cnt
From Room R, Hotel H,Booked B
Where
     H.Hotel_No = B.Hotel_No and
     H.Hotel_No = R.Hotel_No and
     R.Room_No = B.Room_No and
     H.City = "London" and
     B.DateFrom < SYSDATE and
     B.DateTo > SYSDATE
Group By name,type
)
Group By name
             ^

this "select" shows you how many times the most commonly room type booked
AND

select name,type,max(cnt)
from (
Select H.Hotel_Name as name,
          R.Type as type,
          Count(R.Type) as cnt
From Room R, Hotel H,Booked B
Where
     H.Hotel_No = B.Hotel_No and
     H.Hotel_No = R.Hotel_No and
     R.Room_No = B.Room_No and
     H.City = "London" and
     B.DateFrom < SYSDATE and
     B.DateTo > SYSDATE
Group By name,type
)
Group By type
             ^

this one shows you which types are most commonly booked

eventually you can mix these two result and obtain your goal.

I tested by some sample data:

THIS IS THE FIRST SELECT STATEMENT:
mysql>  select  type,name,count(name) from h group by type,name;
+------+-------+---------------+
| type | name| count(name) |
+------+-------+---------------+
| d      | h1   |                  2 |
| d      | h2   |                  7 |
| d      | h3   |                  1 |
| m     | h1   |                  1 |
| m     | h2   |                  2 |
| s      | h1   |                  4 |
| s      | h2   |                  2 |
| s      | h3   |                  2 |
+------+------+----------------+
8 rows in set (0.00 sec)

THESE ARE TWO NEXT SELECT STATEMENT:

mysql> select name,type,max(cnt) from s group by type;
+--------+-----+--------------+
| name  | type | max(cnt)   |
+--------+------+-------------+
| h1      | d     |        7        |
| h1      | m    |        2        |
| h1      | s     |        4        |
+--------+-----+--------------+
3 rows in set (0.00 sec)

mysql> select name,type,max(cnt) from s group by name;
+--------+------+-----------+
| name  | type | max(cnt) |
+--------+------+-----------+
| h1      | d     |            4 |
| h2      | d     |            7 |
| h3      | d     |            2 |
+--------+------+-----------+
3 rows in set (0.00 sec)

datas, types and the names are arbitrary, and are just for test. but you can look at both result and compound them to find the final goal.

but I think you better create a stored procedure or use programming to solve the problem.

hope this help

regards
mohammad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.