#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    11
    Rep Power
    0

    Help Creating a 2D Table


    Part of my homework requires me to make a couple of 2D tables. I completed one of the tables with no problem, but I am having trouble with the second one.

    The question I have finished:
    List hotel name and average room price for each room type for all hotels. Your output should look like a 2-D table.
    The finished table:
    Code:
    CREATE OR REPLACE VIEW SubProp AS
    SELECT hName, price, type
    FROM RoomDM JOIN HotelDM USING (hNo)
    WHERE type IN (‘Single’)
    UNION
    SELECT hName, price, type
    FROM RoomDM JOIN HotelDM USING (hNo)
    WHERE type IN (‘Double’)
    UNION
    SELECT hName, price, type
    FROM RoomDM JOIN HotelDM USING (hNo)
    WHERE type IN (‘Family’)
    /
    
    CREATE OR REPLACE VIEW TabDetail AS
    SELECT *
    FROM (	
     	SELECT
     	hName,
     	AVG(DECODE(type, ‘Single‘, price))Single,
     	AVG(DECODE(type, ‘Double‘, price))Double,
     	AVG(DECODE(type, ‘Family’, price))Family
     	FROM SubProp
     	GROUP BY hName
     	)
    /
    
    SELECT hName, TO_CHAR(Single, ‘$999,990.99’) Single,
     	TO_CHAR(Double, ‘$999,990.99’) Double,
     	TO_CHAR(Family, ‘$999,990.99’) Family	
    FROM TabDetail;
    The Result to the finished table:
    Code:
    HNAME                     SINGLE       DOUBLE       FAMILY
    ------------------------- ------------ ------------ ------------
    Holiday Inn                     $47.49       $84.99      $129.99
    The Redmont Hotel               $44.99       $84.99      $114.99
    University Inn and Suites       $64.99       $99.99      $119.99
    
    3 rows selected.
    The Question I am having trouble with:
    Develop SQL code in Oracle that generates a statistical report of hotels verse room types in 2-D table format.
    The table I am having trouble with:
    Code:
    CREATE OR REPLACE VIEW SubProp AS
    SELECT hName, type, COUNT(type) AS tCount
    FROM RoomDM JOIN HotelDM USING (hNo)
    WHERE type IN (‘Single’)
    GROUP BY hName, type
    UNION
    SELECT hName, type, COUNT(type) AS tCount
    FROM RoomDM JOIN HotelDM USING (hNo)
    WHERE type IN (‘Double’)
    GROUP BY hName, type
    UNION
    SELECT hName, type, COUNT(type) AS tCount
    FROM RoomDM JOIN HotelDM USING (hNo)
    WHERE type IN (‘Family’)
    GROUP BY hName, type
    /
    CREATE OR REPLACE VIEW TabDetail AS
    SELECT *
    FROM (	
     	SELECT
     	hName,
     	COUNT(DECODE(type, ‘Single‘, tCount))Single,
     	COUNT(DECODE(type, ‘Double‘, tCount))Double,
     	COUNT(DECODE(type, ‘Family’, tCount))Family
     	FROM SubProp
     	GROUP BY hName
     	)	NATURAL JOIN (
     				SELECT COUNT(type)Total
     				FROM SubProp
     				)
    /
    BREAK ON REPORT
    COMPUTE SUM LABEL TOTAL OF Single ON REPORT
    COMPUTE SUM LABEL TOTAL OF Double ON REPORT
    COMPUTE SUM LABEL TOTAL OF Family ON REPORT
    COMPUTE SUM LABEL TOTAL OF Total ON REPORT
    SELECT *
    FROM TabDetail;
    The result to the table I am having trouble with:
    Code:
    HNAME                         SINGLE     DOUBLE     FAMILY      TOTAL
    ------------------------- ---------- ---------- ---------- ----------
    Holiday Inn                        1          1          1          9
    The Redmont Hotel                  1          1          1          9
    University Inn and Suites          1          1          1          9
                              ---------- ---------- ---------- -----------
    TOTAL                              3          3          3         27
    
    3 rows selected.
    Looking at the result of this table it is obvious the numbers are wrong, but as I compare my code from the two tables I cannot see what I am doing wrong.

    I went ahead and determined what the result to the table would look like if done correctly, and it should look something like this table below.

    This is what the result should look like:
    Code:
    HNAME                         SINGLE     DOUBLE     FAMILY      TOTAL
    ------------------------- ---------- ---------- ---------- ----------
    Holiday Inn                       15         10          5         30
    The Redmont Hotel                  9          7          4         20
    University Inn and Suites          5          5          2         12
                              ---------- ---------- ---------- -----------
    TOTAL                             29         22         11         62
    
    3 rows selected.
    HotelDM (hNo, hName, street, city, state, zipcode)
    RoomDM (rNo, hNo, type, price)
    In HotelDM hNo is the primary key
    In RoomDM rNo is the primary key and hNo is a primary and foreign key

    I will appreciate all the help I can get. Thank you.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool


    Try this:
    Code:
    CREATE OR REPLACE VIEW TabDetail AS
    SELECT *
    FROM (	
     	SELECT
     	hName,
     	SUM(DECODE(type, 'Single' , tCount,0)) Single,
     	SUM(DECODE(type, 'Double', tCount,0)) Double,
     	SUM(DECODE(type, 'Family' , tCount,0)) Family
     	FROM SubProp
     	GROUP BY hName
     	)	NATURAL JOIN (
     				SELECT COUNT(type)Total
     				FROM SubProp
     				)
    /
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    11
    Rep Power
    0
    Code:
    HNAME                         SINGLE     DOUBLE     FAMILY      TOTAL
    ------------------------- ---------- ---------- ---------- ----------
    Holiday Inn                        3          3          3          9
    The Redmont Hotel                  3          3          3          9
    University Inn and Suites          3          3          3          9
                              ---------- ---------- ---------- -----------
    TOTAL                              9          9          9         27
    
    3 rows selected.
    That's what I am getting with your suggestion. That still does not look right. Thank you for taking the time to help me though.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool


    Originally Posted by Daniel M.
    ...
    That's what I am getting with your suggestion. That still does not look right. Thank you for taking the time to help me though.
    Perhaps if you provide the complete data with create table and inserts, we may be able to troubleshoot better instead of having to "guess" what may be wrong.

IMN logo majestic logo threadwatch logo seochat tools logo