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.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
