(Solved): CIS 505 : HW2: SQL (I) (Total Points: 45) Individual assignment...

CIS 505 HW2:

SQL (I) (Total Points: 45)
Individual assignment

Section:

Name: 

Guidelines:

• Please note that this assignment contains 10 questions.   
• You need to submit two files to this homework. One is your FINAL sql code (.sql), and one is this word document with your answers. Please name your files  HW2_lastname_firstname (i.e., HW2_Chen_Peiyu.sql and HW2_Chen_Peiyu.doc or docx), and submit your files to canvas (following appropriate hw submission link) by the specified deadline.   
• By submitting your homework, you acknowledge that it is submitted as individual work according the Academic Integrity Policy and the W.P. Carey Honor Code to the best of your ability. Please put the following sentence after your name: “This homework is submitted as individual work according the Academic Integrity Policy and the W.P. Carey Honor Code to the best of my ability”. If it is not true, state why it is not true.

Objective
This exercise will enable you to demonstrate mastery of fundamental SQL queries on a DBMS.

Instructions: 

• Use MySQL workbench and connect to the course server. The database to use is apps_and_crunchbase. The relevant schema (the apps schema) is provided as a separate file along with this homework. Please make sure you execute the following statement before writing your queries. 
• use apps_and_crunchbase;
• Develop the solution to each question, and paste the SQL query in the space provided below each question. Please make sure you provide queries that would retrieve just the information asked in the question, no more and no less. For example, if you are asked how many apps, then your query should just return a number. For results reporting, you may be asked to report only a subset of the entire returned results for some questions (for example, report the first 5 rows); for some other questions, you may be asked to report only the number of rows returned by your query. Please report as asked. 

Background of the data:
App Store maintained a few top lists and published top 300 apps in each list every day a few years ago. The top 300 apps in every list were collected every day and stored in sequence in the top300 table that same day (insert_time). For example,
1~300:  top 300 apps for list 1 on day 1
301~600:  top 300 apps for list 2 on day 1
601~900: top 300 apps for list 3 on day 1
So on and so forth

Relevant data of these apps were also collected and stored in the apps table.  

I.    Single Table Queries: Only the apps table is required

1. Retrieve all the attributes for app "Twitter" 

Query: 

Result: 

2.    Retrieve the name and price for the apps whose name contains the string "sun" (hint: can begin with "sun", end with "sun", or have "sun" in the middle (e.g. Samsung) 
Query: 

Row Count: 

3.     Retrieve the number of apps whose seller url is not missing 

Query:

Result: 

4. Retrieve the primary categories (don't show duplicates) 

Query: 

Row count: 

5    Retrieve the min, average, max prices for the primary category ‘Finance’ 
Query: 

Result: 

6.    Show the distribution of the price of apps. In other words, list all of the prices with the count of the apps at a given price.  Please order your results by price in ascending order. (Hint, you should have two columns in your result: price and count. If you are interested in knowing the price structure of apps, you might want to read the first few paragraphs of this article).
Query: 

Result: please copy the first 5 results from your result table which should have more than 5 rows 

Row count: 

7. What is the mean price among the paid apps for each primary category? List in descending order of the mean price. Hint: Paid apps mean price >0. Please consider just the primary category for each app.  Please use the apps table. 

Query:

Results: please copy the first 5 results here

Row count returned: 

8. Give a top 10 countdown of the most prolific app developers (list in decreasing order). Specifically, please report the names of the developers that wrote the most apps and the total number of apps they wrote. (Hint: Two columns, developer name, number of apps, in decreasing order, only the top 10 please)  
Query: 

Result:

9. Retrieve the developer id and name for the developers who developed apps in at least 10 primary categories;

Query: 

Row count: 

10. Are there apps with the same name? Please write a query to show names that are shared by multiple apps and how many apps share each of these common names.  

Query: 

Row count: 

II Multi-Table Queries

11. Retrieve the name of the app that has the highest number of ratings (i.e., rating_count) and the number of ratings it received.  

Query: 

Result: 

12. Retrieve the name and primary category for the apps that are game-center enabled and whose primary category is not "Games" (use apps table). 

Query:

Row count: 

13. In which primary category, the apps have the highest mean average_rating? 

Query: 

Result: 

14. List the total number of ratings (rating_count) received for each primary category? Please list them in descending order of the total number of ratings. 

Query:

Results: (copy first 5 results) 

15. List the primary category, number of ratings and average ratings for the app “Google Earth”. 

Query: 

Result: