-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathfifa19.sql
146 lines (74 loc) · 5.25 KB
/
fifa19.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
create database fifa19;
use fifa19;
select * from tbl_players;
----***Data Analysis on FIFA data***----
--Q1 How many players are there in the datasets?
select count(*) as Total_Players from tbl_players;
--since there are no duplicates count will show total distinct players
--Q2 How many nationalities do these players belong to?
select count(nationality) as number_of_nationalities from tbl_players;
--will show duplicates nationalities as well
select count(distinct nationality) as number_of_nationalities from tbl_players;
--will show unique count of nationalities
--Q3 Which nationality has the highest number of players, what are the top 3 nationalities by # of players?
select count(*) as frequency, nationality from tbl_players group by nationality;
--will show natinality frequency
select count(*) as frequency, nationality from tbl_players group by nationality order by frequency desc;
--will show nationality frequency in decreasing order
select top 3 count(*) as frequency, nationality from tbl_players group by nationality order by frequency desc;
--will show top 3 nationality. In case of MySQL, instead of top 3 at beginning limit 3 is used at the end.
--Q4 What is the total wage given to all players? What's the average and standard deviation?
select sum(wage) as total_wage, avg(wage) as average_wage, STDEV(wage) as std_wage from tbl_players;
--will show data type error varchar
alter table tbl_players
alter column wage float(50);
select sum(wage) as total_wage, avg(wage) as average_wage, STDEV(wage) as std_wage from tbl_players;
--now this will show the results
--Q5 Which player has the highest wage? Who has the lowest?
select max(wage) from tbl_players
--will show highest wage
select name from tbl_players where wage=565000
--will show the name of player with highest wage
select name from tbl_players where wage=(select max(wage) from tbl_players);
--will show name of player with highest wage using sub query
select name from tbl_players where wage=(select min(wage) from tbl_players);
--will show 4770 names with lowest wage
--Q6 The player having the best overall rating? Worst Overall rating?
select name from tbl_players where overall=(select max(overall) from tbl_players);
--will show player with best overall rating
select name from tbl_players where overall=(select min(overall) from tbl_players);
--will show player with worst overall rating
--Q7 Club having the highest total of overall rating? Highest Average of overall rating?
select sum(overall) as total_rating, club from tbl_players group by club order by total_rating desc;
--will show data type error varchar
alter table tbl_players
alter column overall float(50);
select sum(overall) as total_rating, club from tbl_players group by club order by total_rating desc;
--will now show the results. Highest rating-Real Madrid, FC Barcelona, Manchester United
select top 3 sum(overall) as total_rating, club from tbl_players group by club order by total_rating desc;
--will top 3 clubs based on total rating -Real Madrid, FC Barcelona, Manchester United
select top 3 avg(overall) as average_rating, club from tbl_players group by club order by average_rating desc;
--will top 3 clubs based on total rating -Juventus, Napoli, Inter
--Q8 What are the top 5 clubs based on the average ratings of their players?
select top 5 avg(overall) as average_rating, club from tbl_players group by club order by average_rating desc;
--will top 3 clubs based on total rating -Juventus, Napoli, Inter, Real Madrid, FC Barcelona
--Q9 What is the distribution of players whose preferred foot is left vs right?
select count(*) as frequency, [preferred foot] from tbl_players group by [preferred foot] order by frequency desc;
--will show left and right foot distribution. In case of MySQL, numbers can be used instead of column name
--Q10 Which jersey number is the luckiest?
select sum(wage) as total_wage, [jersey number] from tbl_players group by [jersey number] order by total_wage desc;
--will show total wage in desc order wrt to jersey number
select top 1 sum(wage) as total_wage, [jersey number] from tbl_players group by [jersey number] order by total_wage desc;
--will show top jersey number with highest wage
--Q11 What is the frequency distribution of nationalities among players whose club name starts with M?
select count(*) as freq, nationality, club from tbl_players where club like 'M%' group by nationality, club;
--will show nationality frequency based on club name start with M
--Q12 How many players have joined their respective clubs in the date range 20 May 2018 to 10 April 2019(both inclusive)?
select count(*) from tbl_players where joined between '2018-05-20' and '2019-04-10';
--will show the count of players who joined between the given dates
--Q13 How many players have joined their respective clubs date wise?
select count(*) as freq, joined from tbl_players group by joined order by freq desc;
--will show daily joining frequency of players
--Q14 How many players have joined their respective clubs yearly?
select count(*) as freq, year(joined) from tbl_players group by year(joined) order by freq desc;
--will show year wise joining players