Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Unanswered: Noob Join/Query help

    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 :}

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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!

  3. #3
    Join Date
    Oct 2007
    Posts
    3
    Quote 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

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote 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.

  5. #5
    Join Date
    Oct 2007
    Posts
    3
    Quote 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

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Sounds to me like you've done everything correctly. If it were me, I'd leave out the

    Code:
    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

    Code:
    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'

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    However, I'd rather not mix dates and strings as in
    Code:
    b.dateFrom = '2007-10-09'
    but use proper datatype conversion:
    Code:
    b.dateFrom = TO_DATE('2007-10-09', 'yyyy-mm-dd')

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Yeppers, good point ... my bad ... for not seeing that. ---=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •