Need some help with sql urgently please

Databases
1

hi i have an assignment dude tonight and as usual i have left it to the last minute >< . I am having trouble with the last 3 questions and if u are able to help i would really appreciate it i will donate $10 to a charity or your website

the tables are

BOOKING
HOTEL_NO
GUEST_NO
DATE_FROM
DATE_TO
ROOM_NO

GUEST
GUEST_NO
G_NAME
H_ADDRESS

HOTEL
HOTEL_NO
H_NAME
H_ADDRESS

ROOM
ROOM_NO
HOTEL_NO
R_TYPE
R_PRICE

Question 8
For the Sheraton hotel, list (in the ascending order) the total number of bookings for each room type.

SELECT HOTEL_NO, COUNT(*) AS TOTALCOUNT
FROM ROOM, BOOKING
WHERE H.H_NAME = ‘SHERATON’
AND B.HOTEL_NO = R.HOTEL_NO
GROUP BY HOTEL_NO;

Question 9
Find numbers and names of guests who booked rooms (at any hotel) in the price range between 230 and 350 dollars. Show also how many times they booked such rooms.

SELECT GUEST_NO, G_NAME, R_PRICE
FROM GUEST, ROOM
WHERE R_PRICE >230 <350;
(SELECT SUM GUEST_NO FROM BOOKING);

Question 10
Find the maximum average room price (regardless of room type) of all the hotels (Hint: Find the average of each hotel first).

SELECT HOTEL_NO, AVG(R_PRICE) FROM ROOM
GROUP BY HOTEL_NO

2

sorry, man, we won’t do it for you

post a serious attempt at each question, and we’ll help you with advice and corrections

3

by the way, your copy/paste was incomplete, here’s the entire question 10 –

Find the maximum average room price (regardless of room type) of all the hotels (Hint: Find the average of each hotel first).

4

hey thanx for replying man i just updated the original post with my attempts

5

do you have a test database set up for this assignment?

because you should really test your queries, to see the results (or error message) that they produce

6

hey yeh i have it set up

question 8 the message that comes up is

ORA-00918: column ambiguously defined
00918. 00000 - “column ambiguously defined”
*Cause:
*Action:
Error at Line: 5 Column: 10

no idea what it means

question 9 i tried

SELECT GUEST_NO, G_NAME, R_PRICE
FROM GUEST, ROOM
WHERE R_PRICE >230 <350;
(SELECT SUM(*) GUEST_NO FROM BOOKING);

and the message i got was

ORA-00936: missing expression
00936. 00000 - “missing expression”
*Cause:
*Action:
Error at Line: 10 Column: 13

question 10

i get this as the result but im not sure if its right or not

H200 231
H300 310
H100 253.333333333333333333333333333333333333

7

It means what it says. There is a column that is ambiguously defined, in other words you are using a column name in your query that exists in more than 1 table, and because you didn’t specify from which column to take it (alias!) mysql doesn’t know which column to use.

See the line I indicated in red. You can’t confront a column with two values like that. Do a reread of your reference manual and see how to write WHERE conditions.

If you’re not sure, how can we be? We don’t even know the content of your test database.

8

due last night didnt do last 3 questions cause i couldnt figure it out hopefully the others were right and i will just pass :frowning:

9

Well don’t procrastinate :stuck_out_tongue: You know for whatever reason, when I was in school and I had a DB class, I wasn’t all that into it. 5 years later I make my living off it. I really wish I knew back then how important it was and I had pushed the limits of that class. I love to learn everything I can now and find the best methods for everything, I hope you can get to that point soon as well and enjoy this stuff as much as I do, before its too late.