HW1 MySQL query Solution

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