Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
The SQL queries you must write
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2
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.
Assessment
Your work will be assessed according to three criteria:
• Correctness of output (70%)
• Simplicity of code (20%)
• Correctness of formatting (10%)
The “simplicity” requirement means that your code should not be unnecessarily complex. For example, a query that produces correct output, but say, joins more tables than is necessary, may not achieve full marks, even though it produces the right output.
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
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)
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2
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)
Submission
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2
Submit a single PDF showing your ten answers in the format below, to LMS by midnight Friday at the end of week 8. Submit also an SQL (text only) file containing your queries.
Ensure that you place your student number at the top of every page of your submission. In the PDF, for each question, present an answer in the following format:
• Show the question number and question in black text.
• Show your answer (the SQL statement) in blue text (not a screen shot)
• Show a screenshot from Workbench showing output of 10 or fewer lines.
• Show how many rows were returned, in red text