INFO20003代做、代写SQL编程语言、代做SQL代写、代写programmer

日期：2019-09-21 11:23

INFO20003 A2 S2 2019 1

INFO20003 Semester 2, 2019

Assignment 2: SQL

Due: 6:00pm Friday 20th of September

Submission: Via LMS https://lms.unimelb.edu.au

Wellbeing Application

You are the SQL programmer for a mental wellbeing app, which allows users to do two types of

things. The first is to participate in a Twitter-style social network. The second is to do online units

called Steps, which are interactive therapy modules designed to exercise and develop a range

of psychological skills.

How the app works

When users register into the system, a record is created in the ‘user’ table. Upon registering, a

user can follow other users to see their posts and activity. If user x (following) starts following

user y (followed), an appropriate entry is created in the ‘user_follow’ table (x following y does not

necessarily mean that y follows x). The weight attribute of this table holds an integer that reflects

the extent to which x follows y; for example, this number would increase the more x likes or

comments on posts created by y. To help connect users, each user can also select, from a list

stored in the ‘interest’ table, interests that they have (e.g. movies, music, books). For each

interest that a user selects, a record is created in the associative table ‘user_interest’.

On the therapy side of this app, users can do online modules called Steps. Different Steps take

different amounts of time to complete and users can do a Step in one sitting or return to a Step

and complete it later on. Sometimes they might not finish a Step and will instead move on to

something else. Also, they can do any given Step as many times as they would like and each

time, they can rate how it made them feel (1 being the lowest and 5 being the highest).

As soon as user x makes a start on doing Step z, an entry is created in the ‘step_taken’ table to

record the fact that x started doing z. The timestamp of this start is also automatically recorded

in the ‘when_started’ column. If the user successfully completes the Step, the datetime of

completion is recorded in the ‘when_finished’ column.

INFO20003 A2 S2 2019 2

Steps are also categorised under themes, which are used to indicate what types of things a Step

might help with. For example, a Step that helps with mindfulness would be categorised under

the ‘Mindfulness’ theme. A given step can be categorised under more than one themes.

Data Model

Setup Script

A dataset is provided against which you can test your solutions to the assignment. To set up the

dataset, download the file wellbeingapp.sql from the Assessments folder on LMS and run it in

MySQL Workbench. This script creates the database tables and populates them with data.

The script is designed to run against your account on the Engineering IT server

(info20003db.eng.unimelb.edu.au). If you want to install the schema on your own MySQL Server

installation, uncomment the BYOD section at the beginning of the script.

INFO20003 A2 S2 2019 3

The SQL Tasks

In this section are listed 10 questions for you to answer. Write one (single) SQL statement per

question. Subqueries and nesting are allowed within a single SQL statement – however, you will

be penalized for writing overly complicated SQL statements. DO NOT USE VIEWS to answer

questions.

1. Find all the ongoing/unfinished steps. Display the title of these steps and full names of

the users who are taking these steps. (1 mark)

2. List the themes and the number of the steps associated with these themes. Display the

theme name and number of associated steps sorted in descending order. (1 mark)

3. Which step is the least popular based on the average rating given by users? Display the

title and ID of the step and its average rating (formatted to 2 decimal places). Only include

those steps which are rated by at least one user. (1 mark)

4. Find the steps that are taken the greatest number of times. Display the ID, title and count

of the times the step has been taken. In case of ties, display all the steps with the same

number of times taken. (2 marks)

5. Who is the most followed user between age of 15 and 18? Display the age (as an integer),

first name, and last name of such user along with the number of followers. (2 marks)

Hint: The function TIMESTAMPDIFF can be used to subtract two timestamps. The

function CURDATE returns current date.

6. Find all steps that are never taken or are taken exactly once? Display the id and title of

these steps along with the indication how many times the step has been taken (0 or 1).

(2 marks)

7. Find users who started taking step ‘Doing and being’ after they had started the step ‘Panic’

but have never completed ‘Panic’. Display the user ID, first name and last name.

(2 marks)

8. What finished steps were completed both by a user with first name "Alice" and a user

with first name "Bob"? Display the ID and title of such steps along with the number of

times each user has completed these steps. (3 marks)

9. Find the top two users with the highest number of interests. For those two users, find out

the common steps taken by both of them. Display the titles of the common steps they

have taken and the number of times those steps are taken by each user. (3 marks)

10. For each user taking a step, calculate how many other users have taken the same step.

We are only interested in the cases where the step is performed by at least 5 other users.

Display the user ID, number of other users (at least 5 other users) who are taking the

same step and the title of the taken step. (3 marks)

INFO20003 A2 S2 2019 4

Submission Instructions

Submit a single PDF showing your answers to all questions to the Assessment page on LMS by

6pm on the due date of Friday 20th of September. Name your file 987654.pdf, where 987654

corresponds to YOUR student id. Other formatting requirements are listed below.

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 screenshot).

? Show a screenshot from Workbench containing the output of the query.

o If the query returns more than 10 rows, take a screenshot of only the first 10 rows.

? Show how many rows were actually returned in red text.

? Show each query on a separate page.

Example:

QXX. List the names of the themes.

SELECT name

FROM theme;

7 rows returned

INFO20003 A2 S2 2019 5

Requesting a Submission Deadline Extension

If you need an extension due to a valid (medical) reason, you will need to provide evidence to

support your request by 9pm, Thursday 19th of September. Medical certificates need to be at

least two days in length.

To request an extension:

1. Email Farah Khan (khanf1@unimelb.edu.au) from your university email address,

supplying your student ID, the extension request and supporting evidence.

2. If your submission deadline extension is granted you will receive an email reply granting

the new submission date. Do not lose this email! Replies may take up to 12 hours, so

please be patient.

Reminder: INFO20003 Hurdle Requirements

To pass INFO20003 you must pass two hurdles:

? Hurdle 1: Obtain at least 50% (15/30) or higher for the three assignments (each worth 10%)

? Hurdle 2: Obtain a grade of 50% (35/70) or higher for the End of Semester Exam

Therefore, it is our recommendation to students that you attempt every assignment and every

question in the exam.

GOOD LUCK!