Description
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.csvand
player_statisstic.csvwe 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) |
|
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 matchand player_statisticas 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 mustsubmit 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.
Plagiarism is not allowed, you will get 0 points when we found that happened.