==============================================================================================
Part 1 - Due Feb 17, 2012, 5:00 PM (Total points 60: 4 each)
==============================================================================================

0. List all state names and their 2-letter codes.  
Output columns: name, statecode
Order by the state name.

=== 0
select name, statecode
from states
order by name;

**************** RESULT ***************************************************************
NAME                 ST
-------------------- --
Alabama              AL
Alaska               AK
Arizona              AZ
Arkansas             AR
California           CA
...

=============================================================================================

1. Write a query to change your password. Ofcourse when submitting the
answer, don't write the new password (write "****" instead).

=== 1 
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

2. Figure out how to find out all the attributes and their types for a
given table in SQL*Plus, and write down the statement and its result on
the "senators" table. This kind of a statement is usually unique to the
client, and is not part of standard SQL.

=== 2 
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

3. Write a query to report the information for all counties whose names start with "Prince"
(Hint: Use "like"). 
Output columns: name, statecode, populate_1950, populateion_2010
Order by state code.

=== 3
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

4. Write a single query to list only the population in year 2010 for the state represented by Sen. Richard Lugar.
Output column: populate_2010

=== 4 
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

5. Write a single query to report only the total number of the counties in 'Maryland'. The query
should not hard-code the state code for Maryland.

=== 5
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

6. Write a single query to find the name of the state that was admitted last into the union.
Hint: Use nested subquery.

=== 6
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

7. Write a query to find the names of the counties whose names are at least 20 characters long. 
Keep in mind that county name is defined to be a fixed-length 30 character string, so you must
elimiated the padded whitespaces to correctly answer the query.

Output columns: name (of the county)
Order by: name.

=== 7
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

8. (Using the WITH Construct) Write a query to find the name of the state with
the minimum number of counties by first creating a temporary table using the "WITH"
construct. If there are multiple such states, all should be reported.
Output column: name (of the state)

=== 8
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

9. Create a view called HighPopulationCounties containing the high-population counties (defined 
to be counties with at least 2.5M population in 2010), and their state names.
View columns: countyname, statename, population_2010

Report result of: 
"select * from HighPopulationCounties order by population_2010 desc;"

=== 9
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

10. Write a query to find if any senators represent a state that was admitted to the union 
after they were born. Since we don't have birthdates for senators, use only years. So 
if a senator is born in 1950 and the state was admitted in 1950, it DOES NOT count.

Hint: Use "extract" function that operates on the dates. 

Output columns: senator_name, year_born, state_name, year_state_admitted
Order by senator name.

You might want to increase the output width in sqlplus using "set linesize 200;".

=== 10
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================


11. Find all democratic (i.e., with affiliation = 'D') senators that are not chairman of 
any committee or sub-committee. Hint: Use "not exists" or "not in". 

Output columns: name
Order by name.

=== 11
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

12. Create a table (call it PopulationGrowth), using "create as" that contains three 
columns: statename, growth_1900_to_1950, growth_1950_to_2000, where the 
growth_1900_to_1950 is defined as: population_1950/population_1900, and the second
number is defined similarly. 

List the result of:
"select * from PopulationGrowth where growth_1950_to_2000 > 5 order by statename;" 

=== 12
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

13. If you do "select name, admitted_to_union from states order by admitted_to_union", you will notice that the 
only the last two digits of the year are output. Write a query that outputs the 
result of the query more appropriately, i.e., with 4 digits of the year.

Output columns: name, admitted_to_union
Order by: admitted_to_union.

Truncate the answer to 5 rows when pasting the answer below.

=== 13
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

14. Write a query to find all the states whose growth rate was higher by a factor of 2 or more 
in the period 1900 to 1950, than in the period from 1950 to 2000? Use PopulationGrowth table 
created above. For example, if a state grew by a factor of 3 between 1900 and 1950, but only
by a factor of 1.45 between 1950 and 2000, then it would qualify for the answer.

Output columns: name, growth_1900_to_1950, growth_1950_to_2000

=== 14
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================

15. Write a query to find the day when the 30th state was admitted to the union, and the 
name of the state.
Output columns: name, admitted_to_union

=== 15
<INSERT YOUR QUERY HERE>
**************** RESULT ***************************************************************
<INSERT YOUR RESULT HERE>
=============================================================================================
