Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
March 12th, 2012, 05:07 PM
 Daniel M.
Registered User

Join Date: Mar 2012
Posts: 11
Time spent in forums: 8 h 44 m 54 sec
Reputation 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
March 14th, 2012, 10:46 AM
 LKBrwn_DBA
Contributing User

Join Date: Sep 2006
Posts: 813
Time spent in forums: 1 Week 17 h 44 m 6 sec
Reputation Power: 388

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
)
/```

__________________

#3
March 14th, 2012, 03:35 PM
 Daniel M.
Registered User

Join Date: Mar 2012
Posts: 11
Time spent in forums: 8 h 44 m 54 sec
Reputation 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.

#4
March 15th, 2012, 11:18 AM
 LKBrwn_DBA
Contributing User

Join Date: Sep 2006
Posts: 813
Time spent in forums: 1 Week 17 h 44 m 6 sec
Reputation Power: 388

Quote:
 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.

 Viewing: Dev Shed Forums > Databases > Oracle Development > Help Creating a 2D Table