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 material that is published or on the Web.

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.

Requirements

• use one or several views to help answer any of these questions.

Questions

Write queries and add them to a file called test.sql in the same sequence as the questions. You may have to use several queries to answer a question.

Question 1

What day was the coldest as recorded in Weather?

Question 2

Generate a list of all the students who submitted trips, and for each one show the number of trips submitted.

Question 3

Generate a list of all the students and the brand of phone they are using (not the Id, the name of the phone).

Question 4

When was the first trip submitted?

Question 5

How many days has the experiment been going on? In other words how many days are there between the oldest and most recent trip?

Question 6

Missie just lost her phone and decided to replace it by a Nokia. Write a query that uses only the constants Missie and Nokia and that changes her phone to a Nokia. Write another query that shows that the change was correctly implemented.

Question 7

What phone (brand name please) do first year and sophomore students use?

Question 8

Generate a list of all the users (by name), their phone (brand name), their status (shown as "First Year", "Sophomore", "Junior", or "Senior", not 'F', 'O', 'J', or 'S', and the date they submitted each trip. Below is an example

            |  Marie  |  iPhone   |  First Year  |   2010-10-20

Question 9

Claire decided that she wanted to remove all her trips from the system. Write a query (or several queries) that use only the constant Claire and that remove her trips from the table Trip. Write an additional query that shows the contents of the table after the deletion.