HW1 MySQL query Solution

$30.00 $24.00

Overview In this homework, you will learn how to use MySQL and write the SQL statements to query information from a database. Environment MySQL 5​.7 We recommend you using the CMD​ interface rather than the GUI interface​,for the detail of the environment setup please refer to MySQL​_installation​. Dataset Data is modified from Kaggle​.​Please use the…

5/5 – (2 votes)

You’ll get a: zip file solution

 

Description

5/5 – (2 votes)

Overview

In this homework, you will learn how to use MySQL and write the SQL statements to query information from a database.

Environment

  • MySQL 5.7

We recommend you using the CMDinterface rather than the GUI interface​,for the

detail of the environment setup please refer to MySQL_installation​.

Dataset

Data is modified from Kaggle.Please use the two datasets match​.csv​and

player_statisstic.csv​we provided. We also provide the basic data type for each attribute, it may help you to choose a suitable data type when creating a table in MySQL. Also, try to import your data in MySQL client, there is no mysqlimport in the onsite exam.

match.csv

Columns

Description

matchId ​(char)

ID to identify match

matchDuration

Duration of match in seconds

(int)

matchType (char)

String identifying the game mode. The standard modes are

solo”, “duo”, “squad”, “solo-fpp”, “duo-fpp”, and “squad-fpp”;

other modes are from events or custom matches

maxPlace (int)

Worst placement in the match. This may not match with

numGroups, as sometimes the data skips over placements

numGroups ​(int)

Number of groups in the match

player_statistic.csv

Columns

Description

Id ​(char)

Player’s Id

groupId ​(char)

ID to identify a group within a match. If the same group of

players plays in different matches, they will have a different

groupId each time

matchId ​(char)

ID to identify a match

assists (int)

Number of enemy players this player damaged that were

killed by teammates

boosts (int)

Number of boost items used

damageDealt

Total damage dealt

(double)

DBNOs (int)

Number of enemy players knocked

headshotKills (int)

Number of enemy players killed with headshots

heals (int)

Number of healing items used

killPlace (int)

Ranking in the match of number of enemy players killed

killPoints (int)

Kills-based external ranking of the player

kills (int)

Number of enemy players killed

killStreaks (int)

Max number of enemy players killed in a short amount of

time

longestKill

The longest distance between player and player killed at the

(double)

time of death

rankPoints (int)

Elo-like ranking of the player

revives ​(int)

Number of times this player revived teammates

rideDistance

Total distance traveled in vehicles measured in meters

(double)

roadKills (int)

Number of kills while in a vehicle

swimDistance

Total distance traveled by swimming measured in meters

(double)

teamKills (int)

Number of times this player killed a teammate

vehicleDestroys

Number of vehicles destroyed

(int)

walkDistance

Total distance traveled on foot measured in meters

(double)

weaponsAcquired

Number of weapons picked up

(int)

winPoints (int)

Win-based external ranking of the player

winPlacePerc

This is a percentile winning placement, where 1 corresponds

(double)

to 1st place, and 0 corresponds to the last place in the match

Problems

Write the queries for the following problems, your submission will be the queries, not the results. We provide an example output for each of the problems, check if your query gives a similar result, don’t need to care about the header and precision of

floating point number. Usematch​and player_statistic​as your table name.

  1. Please list the maximum number of enemy knockdowns (DBNOs) per match (matchId), and sort by the number of knockdowns (DBNOs) from top to bottom and then list top 20.

請列出每場比賽(matchId)擊倒敵人數(DBNOs)最多的數量,並根據擊倒數量(DBNOs)的數量由大到小排列,取前20筆列出

e.g.

matchId

DBNOs

fc0bbecba8db99

53

6ee2c835176181

40

20 x 2

  1. Please list the player ID, match ID and total damage dealt(damageDealt) which total damage dealt (damageDealt) is between 2000 and 2010.

請列出單場總傷害量(damageDealt)介在20002010之間的玩家Id、比賽Id及單場總傷害量

e.g.

Id

matchId

damageDealt

9d419cd9ca1fd4

3ca359d66f287d

2003

1d1619a5d11431

80cb5dd8fb5554

2009

19 x 3

  1. Please list the types of matches with “fpp” in the match type (matchType) and the number of each match type, then sort by the count from small to large.

請列出比賽類型(matchType)中有“fpp”的比賽類型及每個比賽類型的比賽數量,根據數量由小到大排序列出

e.g.

matchType

count

flarefpp

9

crashfpp

73

8 x 2

  1. Please find the players who have participated in a match which the number of teams (numGroup) is less than or equal to 10, and list the top 20 player ID in the average number of kills in all games and their average number of kills (kills).

請列出那些曾經參加過比賽小組數目(numGroup)小於等於10的玩家,其所有的比賽平均殺敵數前20名多的玩家ID(Id)及其所有的比賽平均殺敵數(kills)

e.g.

Id

avgKills

907341602ad262

46

f24af40d9db7f5

44

20 x 2

  1. Please list the average duration (matchDuration) of each match type (matchType), and sort the result using average duration in ascending order.

請列出每種比賽類型(matchType)的平均時長(matchDuration),並按時長由小到大排

e.g.

matchType

averageDuration

crashfpp

892.7260

crashtpp

894.8000

16 x 2

  1. Please list walk distances, swim distance, ride distance and the total distance between the players who have walked, swam and ridden on vehicles in a game, and the total distance is from large to small. List the first 10 rows of data(using query).

請列出一場比賽裡有走過路(walkDistance)、有游過泳(swimDistance)、有乘過載具(rideDistance)的玩家中,該三項距離跟合計距離是多少,並按合計距離由大至小排列,只列出頭10筆資料即可

e.g.

totalDistance

walkDistance

swimDistance

rideDistance

31411.38

1328

53.38

30030

28702.7

1799

283.7

26620

10 x 4

  1. Please show how many players who join a match with duration (matchDuration) higher than the average duration of all competitions, and do not have any damage (damageDealt) but win the first place (winPlacePerc). Also, show their maximum medical supplies used (heals).

請列出比賽時長(matchDuration)超過所有比賽平均時長的比賽中,沒做過任何傷害(damageDealt)最後卻拿到第一名(winPlacePerc)的玩家有幾位以及他們的最大醫療用品使用量(heals)

e.g.

numberOfPlayers maxHeals

1 x 2

  1. In the match type of “squad” and “squad-fpp”, please list the average ranking of teams (winPlacePerc) with the same number of team road kills (roadKills), and sort the total number of kills by descending order.

請列出比賽類型(matchType)squad”squad-fpp”中,隊伍合計開車擊殺數

(roadKills)一樣的隊伍所得到平均名次(winPlacePerc),並按合計開車擊殺數量由大至

小排列

Note:隊伍的意思為一場比賽裡的一隊,同一隊人玩了N場比賽當成N

e.g.

teamRoadKills avgWinPlacePerc

8

0.5060

7

0.6411

….

9 x 2

Bonus

Feel free to think. Any valuable observation with the explanation.

請自由發想 SQL,找出有趣的資訊並解釋它的含意 ,請附上你的解釋描述、SQL語法及結果截圖

Hint

You can create indexes to speed up your query:) 如果你的query 執行的太慢建立index可以加快速度

Submission

  • You are required to hand in your homework before 2019/03/2723:59​.

  • Late submission will have a penalty of 15%per day​,and we will not accept submission after 4 days of the deadline.

To hand in, you need to upload a zip file that contains your answer to newE3, the

structure of files are listed below. You must​​submit your homework with correct format, otherwise you will get a 30%​​penalty due to incorrect submit format.

0123456.zip (studentID.zip

`–0123456 (this is a folder

|–q1.sql (your query for question 1 |–q2.sql (and so on … |–q3.sql

|–q4.sql

|–q5.sql

|–q6.sql

|–q7.sql

|–q8.sql

`–bonus.pdf (your bonus explanation

Discussion Forum

In this course, we will use HackMD to be our discussion forum.

If you have any problem or question, try to survey the existing problem to see whether sb has asked it on the page.

HW1 discussion <HackMD>

Plagiarism is not allowed, you will get 0 points when we found that happened.

HW1 MySQL query Solution
$30.00 $24.00