============================================================================================================ Part 2 - Due Feb 22, 11:59 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 with temp as ( select states.name, count(*) as num from states, senators, committees where states.statecode = senators.statecode and senators.name = committees.ranking_member group by states.name ) select name from temp where temp.num = (select max(num) from temp); **************** RESULT *************************************************************** NAME -------------------- South Carolina Alabama ============================================================================================= 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 with temp as ( select * from states where admitted_to_union > (select admitted_to_union from states where name = 'Minnesota') ) select name, to_char(admitted_to_union, 'MM-DD-YYYY') from temp where admitted_to_union = (select min(admitted_to_union) from temp); **************** RESULT *************************************************************** NAME TO_CHAR(AD -------------------- ---------- Oregon 02-14-1859 ============================================================================================= 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 alter table committees add num_subcommittees integer; update committees c1 set num_subcommittees = case when parent_committee is not null then -1 else (select count(*) from committees c2 where c2.parent_committee = c1.id) end; select id, parent_committee, num_subcommittees from committees where name like 'A%' order by id; **************** RESULT *************************************************************** ID PAREN NUM_SUBCOMMITTEES ----- ----- ----------------- ANF 5 APR 12 APR1 APR -1 ARM 6 ARM1 ARM -1 CST1 CST -1 FOR3 FOR -1 JUD1 JUD -1 JUD2 JUD -1 ============================================================================================= 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 with tempone as ( select s1.name as state_one, s2.name as state_two, s2.admitted_to_union - s1.admitted_to_union as gap from states s1, states s2 where s1.admitted_to_union < s2.admitted_to_union ), temptwo as ( select state_one, min(gap) as min_gap from tempone group by state_one) select * from tempone where gap = (select max(min_gap) from temptwo); **************** RESULT *************************************************************** STATE_ONE STATE_TWO GAP -------------------- -------------------- ---------- Arizona Alaska 17125 ============================================================================================= 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 with temp as ( select statecode, sum(population_2010) as pop_sum from counties group by statecode ) select name, population_2010, pop_sum from states, temp where states.population_2010 != temp.pop_sum and states.statecode = temp.statecode; **************** RESULT *************************************************************** NAME POPULATION_2010 POP_SUM -------------------- --------------- ---------- Arizona 6329017 6392017 Connecticut 3518288 3574097 ============================================================================================= 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 drop table DemocraticSenators; create table DemocraticSenators as select senators.name as senatorname, states.name as statename from senators, states where senators.statecode = states.statecode and affiliation = 'D'; create or replace trigger UpdateDemocraticSenators after insert or delete on senators referencing new as newRow old as oldRow for each row when (newRow.affiliation = 'D' or oldRow.affiliation = 'D') declare statename char(20); begin if INSERTING then select name into statename from states where statecode = :newRow.statecode; insert into DemocraticSenators values(:newRow.name, statename); elsif DELETING then delete from DemocraticSenators where senatorname = :oldRow.name; end if; end UpdateDemocraticSenators; / show errors trigger UpdateDemocraticSenators; **************** RESULT *************************************************************** SQL> 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%'; SENATORNAME STATENAME ---------------------------------------- -------------------- Mark Begich Alaska SQL> 1 row created. SQL> SENATORNAME STATENAME ---------------------------------------- -------------------- Mark Begich Alaska XYZ Alaska SQL> 1 row deleted. SQL> SENATORNAME STATENAME ---------------------------------------- -------------------- Mark Begich Alaska ============================================================================================= 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: ... ... ... ... ... 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 set SERVEROUTPUT on; declare prev_s char(20); current_s char(20); prev_d date; current_d date; max_so_far integer; cursor c1 is select name, admitted_to_union from states order by admitted_to_union; begin open c1; fetch c1 into prev_s, prev_d; fetch c1 into current_s, current_d; max_so_far := current_d - prev_d; loop prev_d := current_d; fetch c1 into current_s, current_d; exit when c1%notfound; if max_so_far < current_d - prev_d then max_so_far := current_d - prev_d; end if; end loop; close c1; open c1; fetch c1 into current_s, current_d; loop prev_d := current_d; prev_s := current_s; fetch c1 into current_s, current_d; exit when c1%notfound; if current_d - prev_d = max_so_far then dbms_output.put_line('Gap between admissions of ' || trim(prev_s) || ' and ' || trim(current_s) || ' was ' || max_so_far || ' days.'); end if; end loop; close c1; end; / **************** RESULT *************************************************************** Gap between admissions of Arizona and Alaska was 17125 days. =============================================================================================