MCS-274 Lab 3: Intermediate SQL (Spring 2013)

Due April 23, 2013

Background

In this lab, you and an assigned partner will continue working with the election data from lab 1.

Tasks

For each of the following, just turn in your SQL statements.

1. Create your own copies of the SOS's counties and us_senate_results tables using the following two commands:

   create table counties as
   select * from SOS.counties;
   
   create table us_senate_results as
   select * from SOS.us_senate_results;
   

   Now give the appropriate ALTER TABLE statements to add primary key and foreign key constraints such that every county_id that appears in a row of us_senate_results must also appear in a row of counties. Beyond ensuring referential integrity, this may improve the efficiency of joining the two tables. (Think about why.)

2. Create a view called us_senate_results_by_county that is based on the above two tables and provides the total votes for each candidate in each county. The heading of this view and a sample row follow:

   COUNTY_NAME       CANDIDATE_NAME   VOTES
   ----------------- ---------------- -----
   Aitkin            AMY KLOBUCHAR     5628 
   

   As a further test that you've correctly created the view, the following query ought to show you the two counties in far southwestern Minnesota that Amy Klobuchar lost, as well as to whom she lost them:

   select t2.county_name, t2.candidate_name from
   us_senate_results_by_county t1, us_senate_results_by_county t2
   where t1.county_name = t2.county_name
   and t2.votes > t1.votes
   and t1.candidate_name = 'AMY KLOBUCHAR';
   

3. Create an index on your copy of the us_senate_results table that could be used to speed up queries that refer to a specific candidate name, such as the following example:

   select count(*)
   from us_senate_results
   where candidate_name = 'AMY KLOBUCHAR';