[Guldan]

Hi guys, im new to the forum. currently im taking a computer information tech diploma course and am in entry level database.

Ive struggled my way through this assignment.. I don't want free answers but im done everything except one view and a bonus question. Can someone help me ? not just the answer but an explination.. I'm finding Joins and Subqueries very hard to wrap my head around.. anyways here are the two questions:



6) To help the front desk clerk do guest billings, [a - 6 marks, b - 2 marks]
(a) create a view to calculate the account (i.e. running total todate) for each guest at the Grosvenor Hotel; the view should include the room number, guest name, check-in date, check-out date (today), room rate, the duration in days, and the calculated total dollars
(b) write a SELECT query to demonstrate your view (Use 2007-10-09 as current date if needed).
Optional Bonus Question: up to 10 marks but restricted by allowable maximum for course:
7) Find the most commonly booked room type(s) for each hotel in London? For each hotel, list the hotel number, room type and the number of bookings.

Here are the tables, let me know if you need the data inserted.

CREATE TABLE Hotel
(hotelNo NUMBER(4) NOT NULL
,hotelName VARCHAR2(16) NOT NULL
,hotelAddress VARCHAR2(40) NOT NULL
,PRIMARY KEY (hotelNo)
);

CREATE TABLE Room
(hotelNo NUMBER(4) NOT NULL
,roomNo NUMBER(4) NOT NULL
,type VARCHAR2(8) NOT NULL
,price NUMBER(6,2) NOT NULL
,PRIMARY KEY (hotelNo, roomNo)
);

CREATE TABLE Guest
(guestNo NUMBER(4) NOT NULL
,guestName VARCHAR2(15) NOT NULL
,guestAddress VARCHAR2(40) NOT NULL
,PRIMARY KEY (guestNo)
);

CREATE TABLE Booking
(hotelNo NUMBER(4) NOT NULL
,guestNo NUMBER(4) NOT NULL
,dateFrom DATE NOT NULL
,dateTo DATE
,roomNo NUMBER(4) NOT NULL
,PRIMARY KEY (hotelNo, guestNo, dateFrom)
);

thank you, and go easy :}

[Littlefoot]

Basically, CREATE TABLE and INSERT INTO statements are always welcome - those will help other Forum members to create a testing environment.

However, it is not our job to do your homework. You won't benefit from a complete solution as you won't learn anything from it. The right way to do this task is: YOU do the coding. Run it, correct syntax errors and come back to ask a question about the SPECIFIC problem you have (for example, "query returns a result, but rows are not ordered in descending order. How can I do that?"). We'll gladly help!

[Guldan]

Originally Posted by Littlefoot

Basically, CREATE TABLE and INSERT INTO statements are always welcome - those will help other Forum members to create a testing environment.

However, it is not our job to do your homework. You won't benefit from a complete solution as you won't learn anything from it. The right way to do this task is: YOU do the coding. Run it, correct syntax errors and come back to ask a question about the SPECIFIC problem you have (for example, "query returns a result, but rows are not ordered in descending order. How can I do that?"). We'll gladly help!

I understand, but now my assignment is due in 2 hrs and its not done. my view is creating but I have idea if its right.. ive gotten nowhere, wouldve been nice if someone could point me in the right direction

[shammat]

Originally Posted by Guldan

my view is creating but I have idea if its right..

Show us the view, and maybe we can spot any problems.

[Guldan]

Originally Posted by shammat

Show us the view, and maybe we can spot any problems.

CREATE VIEW Running_Total AS
SELECT g.guestNo, g.guestName, g.guestAddress, r.price*(b.dateTo - b.dateFrom) AS S1
FROM guest g, booking b, hotel h, room r
WHERE g.guestNo = b.guestNo AND
r.roomNo = b.roomNo AND
b.hotelNo = h.hotelNo AND
h.hotelName = 'Grosvenor Hotel' AND
b.dateFrom = '2007-10-09';


I dont know what im doing... lol. SQL is tough

to DISPLAY it dont I just

SELECT *
FROM Running_Total

[chuck_forbes]

Sounds to me like you've done everything correctly. If it were me, I'd leave out the

h.hotelName = 'Grosvenor Hotel' AND
b.dateFrom = '2007-10-09'

from the VIEW def'n, and include that only in queries against the view. Otherwise, you have to create a separate view for every hotel/dateFrom combination

CREATE VIEW Running_Total AS 
SELECT h.hotelName, b.dateFrom, g.guestNo, g.guestName, g.guestAddress, r.price*(b.dateTo - b.dateFrom) AS S1
FROM   guest g, booking b, hotel h, room r
WHERE g.guestNo = b.guestNo AND
      r.roomNo  = b.roomNo AND
      b.hotelNo = h.hotelNo

SELECT * 
FROM Running_Total
WHERE h.hotelName = 'Grosvenor Hotel' AND
      b.dateFrom  = '2007-10-09'

[Littlefoot]

However, I'd rather not mix dates and strings as in

b.dateFrom = '2007-10-09'

but use proper datatype conversion:

b.dateFrom = TO_DATE('2007-10-09', 'yyyy-mm-dd')

[chuck_forbes]

Yeppers, good point ... my bad ... for not seeing that. ---=cf