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.



















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




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






















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
















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
















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
















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



















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



















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.