469,898 Members | 1,586 Online

# Trouble with homework

143 100+
OK I'm having trouble with this problem on my homework. I got the rest OK, I don't know why this problem is stumping me.

List the number of rooms in each hotel.

Tables:
HOTEL: (hotelNo, hotelName, city)
ROOM: (roomNo, hotelNo, type, price)
BOOKING: (hotelNo, guestNo, dateFrom, DateTo, roomNo)
GUEST: (guestNo, guestName, guestAddress)

here's what I have tried, but it's not working.....

SELECT hotelNo, COUNT (roomNo) AS roomcount
FROM room
WHERE (SELECT DISTINCT hotelNo FROM hotel)
ORDER BY hotelNo

and

SELECT COUNT (roomNo)
FROM room
WHERE h.hotelNo = r.hotelNo
Order By hotelNo

Any clues or advice and any help will be appreciated.
Jun 24 '07 #1
8 1288
Purple
404 Expert 256MB
Hi teddarr

what is in table room, column roomNo ? is there one record for every room in every hotel or is it one row per hotel with the number of rooms in it ?

Purple
Jun 24 '07 #2
teddarr
143 100+
table room contains one row for every room in every hotel. The table also contains hotelNo which gives the distinct hotel.
Jun 24 '07 #3
debasisdas
8,127 Expert 4TB
Is there any cascade dependency with other tables.

Can you please post what exactly is the error you are getting.
Jun 25 '07 #4
Purple
404 Expert 256MB
Hi teddar,

I think this should do what you need...

Expand|Select|Wrap|Line Numbers
1. SELECT    hotel.hotelNo, COUNT(room.roomNo) AS num_rooms
2. FROM         hotel INNER JOIN
3.                       room ON hotel.hotelNo = rooom.hotelNo
4. GROUP BY hotel.hotelNo, room.roomNo
I think you need to do some reading around joins and group by..

Regards Purple
Jun 25 '07 #5
teddarr
143 100+
Purple,

Your recommendation on reading is right on. That is exactly where my mental hang up is.

I tried your statements and even added a line that reads WHERE hotel.hotelNo = room.hotelNo

The result I get is about 5 entries with the number 1 next to each hotelNo instead of a count.

example:
hotelNo num_rooms
1 1
1 1
1 1
1 1
1 1
1 1
1 1
2 1
2 1
2 1
2 1
3 1
3 1
3 1
3 1
3 1

This gets me closer but I still don't know what to do from here.
Jun 25 '07 #6
Purple
404 Expert 256MB
Hi,

is hotel.hotelNo unique ?

for info - WHERE hotel.hotelNo = room.hotelNo is not required - the join is doing this for you in this : hotel.hotelNo = rooom.hotelNo

do me a :

Expand|Select|Wrap|Line Numbers
1. select * from hotel
and

Expand|Select|Wrap|Line Numbers
1. select * from room
and post some of the output into the thread plse

Purple
Jun 25 '07 #7
teddarr
143 100+
I got it!

Thanks Purple. With yours and the guys at work giving advice, I now have a solution:

SELECT hotel.hotelNo, COUNT(room.roomNo) AS numRooms
FROM hotel INNER JOIN room ON hotel.hotelNo = room.hotelNo
GROUP BY hotel.hotelNo;

You and the guys at work both pointed out that the WHERE clause was redundant. Thanks for the heads up.
Jun 26 '07 #8
Hi,

This is May be Help For u

SELECT H.HotelNo as HotelCode,Count(R.RoomNo),H.CITY as NoOfRooms
FROM Hotel H INNER JOIN ROOM R ON
H.HotelNo=R.HotelNo
Group By H.HotelNo,H.CITY

Bye
Jun 26 '07 #9

### Post your reply

Sign in to post your reply or Sign up for a free account.

### Similar topics

 13 posts views Thread by Kishor | last post: by 9 posts views Thread by suzee_q00 | last post: by 27 posts views Thread by stonemcstone | last post: by 1 post views Thread by redpayne | last post: by 4 posts views Thread by Rico | last post: by 2 posts views Thread by MISSMIS | last post: by 17 posts views Thread by Ganon11 | last post: by 8 posts views Thread by garyrowell | last post: by 24 posts views Thread by Three Headed Monkey | last post: by reply views Thread by eddparker01 | last post: by reply views Thread by isladogs | last post: by reply views Thread by Trystan | last post: by reply views Thread by Trystan | last post: by reply views Thread by xarzu | last post: by 6 posts views Thread by Kwabena10 | last post: by 1 post views Thread by Waqarahmed | last post: by reply views Thread by Salome Sato | last post: by reply views Thread by skydivetom | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.