Lab 3: Intermediate SQL
Started: Thursday, 4/7; Due: Thursday 4/14, by the beginning of class
In this lab, you will continue working with your election data tables from lab 2. If your tables are not usable, let me know and I can provide replacements.
I would like you to work with the same partner as you did in lab 2. Each pair should hand in a single report.
For each of the following, just turn in your SQL statement.
Assuming you have a table of results, or one of state house results, and you have a table of precincts, add the appropriate constraint so that each result needs to correspond to a precinct--assuming you don't already have such a constraint. If any of these assumptions are violated (because you don't have the relevant tables or already have the constraint), let me know and I will give you an alternative assignment.
Create a view that is based on your data tables and is equivalent to the SOS's candidates table. If you already did essentially this, but in the form of a table, let me know and I will give you an alternative assignment.
Create an index that could be used to speed up queries that refer to a specific candidate name (like the queries from lab 1 concerning
'TERRY MORROW'). You should be prepared for the possibility that two candidates (hopefully for different offices) might have the same name.