INFO90002 - Assignment 2 - SQL  -Solved

Case
You are working on an exercise tracking system for a chain of gyms found throughout Australia. The system allows gym members to wear a device that records their movements while running, to encourage them to exercise more. Your job is to write SQL queries that analyse the data. You have been provided with some test data to help you refine your queries. The data model is as follows:

  

Data recorded by the system
 There are several gyms: each has a name and is located in a particular city.  

Each gym member or user wears a device that measures their location once per minute while they are running. The location data are sent to our database for analysis. For example, the map on the left shows the locations that have been recorded by user Alice.

Most users are registered members of the gym. But we also offer the tracking program to members of the public who are not registered at gym members.

Setup Script
To set up the database in your MySQL server, download the file asst2-2019s2-setup.sql from LMS and run it in Workbench. This script creates the database tables and populates them with test data.  

Note the comments near the start of the script. You can run this script in two ways, depending on whether you run it on the UniMelb server or your own server. If it is your own server, you will want to uncomment some lines near the top, so that you create a new schema to store the tables in. You can’t create new schemas on the UniMelb server.

 

The SQL queries you must write
Over the page are listed 10 questions for you to answer. Write one SQL statement to answer each question. Do not use views or temporary tables to answer the questions.

Beside each question is a maximum mark which reflects the difficulty of the question.  Your total score will be scaled to 10% of your overall marks in the subject.  

Your SQL queries should use the same inputs that are used in the questions. For example, your answer to a question that mentions “Alice” should involve a filter on the word “Alice”.

Make sure your code and output are readable – this will affect your mark. Where it would improve readability, order your output and use aliases. Format large numbers and fractions appropriately.



 

Location Data
We use a precision of 4 decimal places: for example, the Doug McDonell building is at  longitude 144.9630, latitude -37.7990 .

 

Calculating Distance
Calculating the distance between two points P1 and P2 requires a complex formula which you can read about at https://en.wikipedia.org/wiki/Haversine_formula . For the purposes of this assignment you can use the following simplified formula based on the Pythagorean theorem, which works well enough in southern Australia:

distance in km = sqrt( (P1.latitude – P2.latitude)^2 + (P1.longitude – P2.longitude)^2 ) * 100 

The Questions      
(marks are in brackets after each question) 

1.     Print each user’s name, along with the number of times they have recorded a location.         (1) 

2.     How many cities are in the same state as Melbourne?  

(Don’t count Melbourne in your answer.)        (1) 

3.     List the names of any members of Academia gym who have been  north of Brunswick gym.      (1) 

4.     How many users are registered with gyms in the state of Vic?       (1) 

5.     What percentage of the total number of users are not affiliated  with gyms?    (1) 

6.     How much time elapsed between the first and last recorded locations  of the user with id 4?     (2) 

7.     Print as two columns: the average number of locations recorded by registered users, and the average number of locations recorded by unregistered users.    (3) 

8.     List the names of users who have run within 100m of the Doug McDonell building.      (DMD is at longitude 144.9630, latitude -37.7990 .)        (3) 

9.     What is the distance between the northern-most and southern-most locations to which Alice has run?       (3) 

10.            Show the total distance that Alice has run. Calculate this by summing the individual distances between each successive pair of locations.        (4)