CSC220 Midterm 2010

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

---

Page under construction!

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.
• Your assignment is to generate queries that answer the 10 questions below, assuming that you have 4 tables in your database named DataPhone, Student, Trip, and Weather, and the dump of which is available here.
• You have to figure out how to use the dump of the 4 tables to generate copies of them in your database. You have several options...

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 the names 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 names of the students and the brand of phone they use.

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

There should be one entry per trip.

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.

Question 10

Same as Question 3, but organize the output by status, i.e. all the first-years together, all the sophomores together, all the juniors together, and all the seniors together. The order in which the years are listed is not important.

Question 11 (Optional and Extra Credit)

Same as Question 10, but figure out a way that does not require to modify the orginal dump of the tables, and that allows you to list the first years first, sophomore next, then juniors, and finally seniors, in that order.

Submission

• Remember the rule "Do not edit before you submit!"
• Store the dump of the four tables in a text file called test.sql
• Add a header to this file that includes your name and account number.
• Add your queries with comments at the end of the file.
• Use the testSql.php program of Homework 5 to check that your queries all execute correctly.
• Submit the file as follows:

   submit   midterm   test.sql