Homework 3

 
Due Date: 11/15/2011
Points: 50
 
We ccontinue to use the table CITIES from
http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population.
 
1) Create a new table CITIES_BY_AREA which looks exactly like CITIES.
Show the first 30 rows.
[2 points]
 
 
 
2) Add a column POPULATION_DIFF at the right to CITIES.
Add a column POPULATION_DIFF_PERCENT at the right to CITIES. 
Show the first 30 rows.
[2 points]
 
b) Write a PL/SQL program that will compute the difference between
a city and the one immediately after it and place this into the
column next to the city name.
 
Thus, New York should get the value of
8,175,133 minus 3,792,621.
in POPULATION_DIFF.
 
Next compute the percentage of this number by dividing the difference by the
population of the larger of the two cities, multiplying
by 100 and rounding to three digits after the decimal point.
This value should be stored in POPULATION_DIFF_PERCENT next to the city name.
[10 points]
 
c) Your program should place NULL values into the two new columns for 
Temecula (the last row). This is not a sseparate program, just a part of b).
Show the first 30 and the last 20 rows.
[2 points]
 
3) Add a column AREA_DIFF at the right to CITIES_BY_AREA.
Add a column AREA_DIFF_PERCENT at the right to CITIES_BY_AREA. [2 points]
 
Then perform the same work as for 2)b) and 2)c) for the table
CITIES_BY_AREA.
Show the first 30 and last 20 rows.
 
[10 + 2 points]
 
 
4) Create a new table CITIES_IN_XML which has only three columns:
RANK (a number)
CITYNAMES (an XML column)
CITYNUMBERS (an XML oolumn)
 
[2 points]
 
5) Write a PL/SQL program that loads all data from CITIES into
CITIES_IN_XML.
 
The data in CITYNAMES should be stored in this format:
 
<NAMES>
 <CITYNAME>New York</CITYNAME>
 <STATENAME>New York</STATENAME>
</NAMES>
 
The data in CITYNUMBERS should be stored in the following format:
 
<NUMBERS>
 <POPULATION>
 <POPULATION_VALUE>
 </POPULATION_VALUE>
 <DIFF_TO_NEXT_CITY>
 </DIFF_TO_NEXT_CITY>
 <PERCENT_DIFF_TO_NEXT_CITY>
 </PERCENT_DIFF_TO_NEXT_CITY>
 </POPULATION>
 <AREA>
 </AREA>
 <DENSITY>
 </DENSITY>
</NUMBERS>
 
Show the first 30 and last 20 rows.
 
[10 points]
 
6) Write Select statements to find the following information from 
CITIES_IN_XML: (NOT FROM CITIES!!!)
 
For all show the SQL code.
For all show the result.
If the result is longer than 60 rows, then show the first 30 and the last 20 rows.
 
a) The city with the largest Area: Show city, state and area.
[1 point]
 
b) All the cities from California, ordered by area.
Show city, state, population and area.
[1 point]
 
c) The total population of each state as much as you can tell
from this table. Show the state and the total population
[2 points]
 
d) The city with the largest percent difference of population, 
of all the cities that have fewer than 1 Million inhabitants.
Show the whole row for this city.
[2 points]
 
e) For every city (except New York) how often it would fit into the
area of New York. Thus, Philadelphia would fit 2.25 times into New York.
Retrieve the Area of New York by a sub-select. 
Do not type in the Area of New York.
Show the whole row for every city and the "how often" value. 
(You can omit the New York row or show "1" for "how often". 
Whatever is easier for you.)
(Round the result after two digits.)
[2 points]
 
 
 
================================================================
 
THE FOLLOWING APPLIES TO ALL ASSIGNMENTS:
 
- Create a nice cover page with 
 your name, assignment number, class number and a descriptive
 title of the assignment.
 
 All parts of your assignment need to be stapled 
 together, or held together by a folder. Points will be taken off
 for not complying with these rules.
 
 If you use a folder, write your name ALSO on the folder. Or use
 a folder with a transparent cover page.
 
 No handwriting anywhere.
 No handwriting anywhere.
 No handwriting anywhere.
 
 Make things look nice and professional. 
 
- Documentation:
 Do a reasonable amount of internal documentation.
 Internal documentation typically means that you explain
 your code or SQL statements.
 No external documentation unless I specifically ask for it.
 
- Test runs:
 Show your table after you build it. Show testruns.
 Show as much as you can. The burden of proof is up to you.
 NO TESTRUNS, NO POINTS. 
 (Or very, very few.) You may record the results in a file.
 Thus, you don't need to do screen dumps.
 You will not be punished for too many test runs.
 
- Hardcopy:
 All code, documentation, and testruns will be accepted as Hardcopy
 (Printout) ONLY. No email, No fax etc.
 
- Grading:
 Grading takes into account correctness of the results, but also
 how well they are documented, and what their visual appearance is.
 Things should look nice and PROFESSIONAL. Not sloppy. Use a spelling
 corrector for your comments or (later on) write-ups. Some people do this
 very nicely. Don't cut corners here, or you will look bad in comparison.
 PROGRAMS MUST BE INDENTED.
 
- Cheating:
 The usual policies apply. 0 points for "suspiciously similar" results.
 Report to the Dean in case of repetition.
 
- Files:
 You must keep all your files of every assignment up to the end of
 the semester. If there is any doubt about your assignment I will
 ask you to demonstrate it online.
 
- Late policy:
 I will take off 10 points for being late up to 1 week. Programs
 that are handed in more than a week late will receive no credit
 at all, unless you have a good reason with WRITTEN documentation
 (usually medical).