============================================================================================================
Part 2 - Due Feb 24, 5:00 PM (Total points 60: 8 each for problems 16 to 20; 10 each for problems 21 and 22)
============================================================================================================

16. Write a query to find the name of the state whose representatives are the ranking members of most committees or subcommittees?
Here the committees and the subcommittees are counted separately.

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

17. Write a query to find the state that was admitted next after 'Minnesota'. If there are
multiple such states (admitted on the same day), all should be reported. Make sure the date
is reported correct (see question 13).
Output: name, admitted_to_union

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

18. Write a query to add a new column called ``num_subcommittees'' to the Committees table.  
Initially the ``num_subcommittees'' column would be listed as empty.  Write a query 
to ``update'' the table to set it appropriately. The columns should be set to 0 if the 
committee has no sub-committees, to -1 if the committee is a sub-committee, and to the 
number of subcommittees otherwise. (Hint: Use CASE).

Report the result of: 
select id, parent_committee, num_subcommittees from committees where name like 'A%' order by id;

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

19. Write a query to find the largest gap (in number of days) between admission of two consecutive
states. For example, the gap between Delaware and Pennsylvania was 5 days, whereas the gap between 
Utah and the next state to be admitted, Oklahoma, was almost 11 years.
Output: state_one, state_two, gap (in days).

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

20. As you can see, there is a population field associated with the state, as well as the 
counties. We would like to make sure that they are consistent, i.e., the population of 
a state is equal to the sum total of populations of its counties. Write a query to check
if there is any violation for the 2010 population.

Output: names of states which violate the property, the two population counts
Order by: state name

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

21. [Trigger] Create a new table: DemocraticSenators(senatorname, statename), containing
all the Democratic senators. Write a "trigger" to keep this new table updated when a new tuple 
is inserted into the "senators" table, or if a row is deleted from that table. 

Note: DBMSs tend to be picky about the syntax. Don't forget the "/" at the end.

Use: "show errors triggername" to see if there were any errors after creation.

Report the results of the following statements after creating the trigger.

select * from DemocraticSenators where statename like 'Alaska%';
insert into senators values ('AK', 'XYZ', 'D', 2012, 2012);
select * from DemocraticSenators where statename like 'Alaska%';
delete from senators where name like 'XYZ%'; 
select * from DemocraticSenators where statename like 'Alaska%';

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

22. [User Defined Functions] Some tasks are hard to write as SQL queries, for example 
Question 19 from above. Other examples include when we are asked to construct an output
in a nested form, e.g., if we wanted to construct an XML document for the committee information:
<committee>
    <name>...</name>
    <rankingmember>...</rankingmember>
    <subcommittee>
        <name>...</name>
        ...
    </subcommittee>
    <subcommittee>
    ...
    </subcommittee>
</committee>

Such things are often much easier to do in an embedded language like PL/SQL
More specifically, see: 
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#ADFNS009

Write a PL/SQL anonymous block that can be directly executed in SQL*PLUS, for doing the 
same task as Question 19 from above. Use the trick that a 'cursor' can be defined to 
scan a table in a sorted order.

Don't forget to use "SET SERVEROUTPUT ON;" when you attempt to execute this using SQL*PLUS.

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