Reference no: EM132293131

Assignment: SQL

PROJECT

Case

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. Your job is to write SQL queries that allow the app to work.

The data model is as follows:

The Questions

1. List the full names (e.g. Alice Smith), as one column, of the users who have not taken any Steps yet.

2. List all of the IDs and titles of Steps that contain the substring ‘mindful' in their title.

3. Provide a list of the titles of all Steps completed by user with id = 17. Do not show duplicates (list each title only once).

4. Provide a list of the titles of all Steps that have been taken more than two times along with a count of how many times.

5. Which Step(s), listed with columns id, title and the count of times taken, have been taken the greatest number of times?

6. List each Step with the title column, along with a count of how many times that Step has been taken and the average rating received by the Step (formatted to 2 decimal places). Order the result by the average rating as a number in descending order.

7. Provide a list of the titles of all Steps that have been taken by both Alice (id = 1) and Bob (id == 2), along with the combined number of times they have taken the Step.

8. List users older than or equal to 21 years of age, along with a count of how many other users they are following and a count of how many other users are following them. List the user's id, first name, last name, age, following count and followed count, and order the results by first name ascending, then last name ascending.

9. For each (user, theme) pair such that user has taken some steps under the theme, provide a count of how many times a user has taken a step that is categorised under the theme. The output should consist of user ID, user first name, user last name, theme name and the count of steps taken.

For question 10, you can attempt one and only one of 10A or 10B:

10.

A) Provide a complete list of all user ID pairs such that the two users follow each other and share at least one interest. (Hint: MySQL has a CROSS JOIN operator, which returns the Cartesian product of rows from the joined tables)

B) A query that returns a row for each instance in which two users, x and y, share an interest z. The result should consist of user x ID, user x first name, user y ID, user y first name and the interest name. The returned results should be such that only one of (x, y) or (y, x) is returned; for example, if row [1, Alice, 2, Bob, Tennis] is in the result set, then [2, Bob, 1, Alice, Tennis] should not be.

Attachment:- Database Systems and Information Modelling.rar