CSC220 Midterm 2010

--D. Thiebaut 00:58, 21 October 2010 (UTC)

---

This midterm examination is given under the rules of the honor code. You cannot discuss any of the material on this exam with anybody else except your instructor. You have to work individually. You can ask questions to your instructor only in class or via email.

You have access to all published and Web materials.

The midterm is due on Thursday, Oct 28th, at 11:59 p.m. + 1 minute.

Problem 1

• For this problem, you have to create a series of queries and store them in a text file called test.sql, the same way you did in the previous homework.
• You have to generate queries that answer the different questions, assuming that you have 4 tables in your database named DataPhone, Student, Trip, and Weather, and the dump of which is available here.

Some Explanations

• DataPhone Table
  • Everything should be self explanatory
• Student Table
  • Status:
    • F for first year
    • O for sophomore
    • J for junior
    • S for senior
    • Phone: whichever model the student is using
• Trip Table
  • Date: the date the trip was recorded. DATE is a type supported by MySQL. It's not a string. Not a VARCHAR.
  • KML: the contents of the KML data sent by the phone. The type of this field is TEXT, a type also supported by MySQL.
  • Xmin, Xmax, Ymin, Ymax represent the coordinates taken from the KML (by some program that doesn't exist yet) that define the surrounding rectangle.
  • StudentId is the Id of the student who recorded the trip.
• Weather Table
  • This table contains the average temperature recorded on a given date.