HW1 MySQL query Solution

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 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. Use​ match​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

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

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

e.g.

Id
matchId
damageDealt



9d419cd9ca1fd4
3ca359d66f287d
2003



1d1619a5d11431
80cb5dd8fb5554
2009




...




19 x 3







    3. 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





    4. 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





    5. 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





    6. 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






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






    8. 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/27​ 23: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.