COMP114 assignment 2 (1 Viewer)

[choco]

i really want to get this assignment over and done with but am already stcuk on the first one
the question is :
Find the average of the room price (regardless of room type) for hotel ‘ANA’ (you assume that you do not know the hotel_no and you disregard the room types).

i have tried this :SELECT AVG(R_PRICE), H_NAME
FROM ROOMS, HOTELS
WHERE H_NAME = 'ANA'

but doesn't work ...i could do it if the avg thing wasn't there.
any tips?

 

[...]

did u link it?

like

select avg(room.r_price),hotel.h_name
from rooms, hotels
where hotel.h_name = 'ANA'

eh, mine could be way off

 

[choco]

i think i am way off.....i thinkwe are supposed to use subqueries...
could some one explain to me how to use it?

 

[IAmNotFat]

q1

brother choco.

First of all, you only want to select from the rooms table. Then, instead of using h_name, use hotel_no.

Then you just reference this number to the number in the hotels table.
To do this add something like:

WHERE hotel_no = (SELECT hotel_no
FROM hotels
WHERE h_name = 'ANA');

 

[IAmNotFat]

Room Pricing??

okay, so how about this.

Are is it supposed to be just assumed that the room prices are a price per night? Now that would be logical, but there is nowhere that this is said in either this assignment or assignment one.
Have you dudes been told in your tutes or pracs?

 

[IAmNotFat]

And another question.
How do I display only the maximum of the 3 values returned by this query?

SELECT AVG(NVL(r_price,0)) AS "Price", hotel_no
FROM rooms
GROUP BY hotel_no
ORDER BY "Price" DESC;

(I've attached a copy of the database on which i'm performing this query)

 

[doe]

SELECT MAX(AVG(NVL(r_price,0))) AS "Price" ....

?

 

[IAmNotFat]

yeah, tried that. doesn't like it....

 

[choco]

HOW WOULD YOU INTERPRET THIS QUESTION?

Find the maximum average room price (regardless of room type) of all hotels.

LIKE DO THEY JUST WANT MAX PRICE OF EACH HOTEL?
OR MAX OF ALL THE AVG PRICE OF HOTELS?

 

[IAmNotFat]

get the average of all the rooms in every hotel, grouped by the hotel.

Then display the maximum of these averages...

 

[choco]

yeah does anyone know if the prices are a day by day basis for the assignment?

 

[doe]

oracle sucks eh

i cant stand it

 

[IAmNotFat]

yeah does anyone know if the prices are a day by day basis for the assignment?

I'm pretty sure they are - according to the answers for assignment 1.

Does anyone know how to add values in one row with values in another and put them out in one row? the ADD function doesn't work. You see, it's for the last question - I can get all the prices for each booking, and all the addresses, but I want to basically group the results by the addresses....GROUP BY doesn't work either...

 

[choco]

CAN SOMEONE HELP ME OUT WITH THIS QUESTION...
Find the difference in the average room price (regardless of room type, i.e. disregard the room type) for hotels ‘ANA’ and ‘Windsor’ (you assume that you do not know the hotel_no for both hotels).
I HAVE THE AVERAGES OF BOTH HOTELS SEPARATELY..

 

[choco]

also can you help me out with the currency format..how do you incorporate it in, can'tyou just assume its in currency format?

 

[clairegirl]

err tell me question three?