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

Due April 16, 2015

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.

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.)
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           | AL FRANKEN          | 3788       |
```
As a further test that you've correctly created the view, the following query ought to show you the counties that Al Franken lost, as well as to whom he lost them. You ought to see that all his losses were in outstate counties and that the winner in those counties was always Mike McFadden.
```
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 = 'AL FRANKEN';
```
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 = 'AL FRANKEN';
```
Check the durations that MySQL Workbench reports for each of several repetitions of this query before and after creating the index. Does it appear that the index did speed up the query? If so, looking at the duration reported for creating the index, can you estimate how many queries like this one would be needed to repay the time invested in creating the index?