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

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.