Logo
Let's Talk
Portfolio

SQL Challenge 2 – Esports Tournament

Dive into the electrifying world of eSports, where elite competitors from around the globe have converged for an intense showdown. As the excitement unfolds in this epic tournament, our mission is to dissect the provided data and unveil the comprehensive insights that encapsulate the essence of this thrilling gaming spectacle. Let the analysis commence!

Problem Statement

The top eSports competitors from across the globe have gathered to battle it out. Can you analyse the following data to find out all about the tournament?

Tables

Here are the tables you will be using:

Esports Tournament Datasets

Schema 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
<span class="line"><span class="cl"><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">teams</span> <span class="p">(</span>
</span></span><span class="line"><span class="cl">    <span class="n">team_id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
</span></span><span class="line"><span class="cl">    <span class="n">team_name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">,</span>
</span></span><span class="line"><span class="cl">    <span class="n">country</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span>
</span></span><span class="line"><span class="cl">    <span class="n">captain_id</span> <span class="nb">INT</span>
</span></span><span class="line"><span class="cl"><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span><span class="line"><span class="cl"><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">teams</span> <span class="p">(</span><span class="n">team_id</span><span class="p">,</span> <span class="n">team_name</span><span class="p">,</span> <span class="n">country</span><span class="p">,</span> <span class="n">captain_id</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="k">VALUES</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'Cloud9'</span><span class="p">,</span> <span class="s1">'USA'</span><span class="p">,</span> <span class="mi">1</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'Fnatic'</span><span class="p">,</span> <span class="s1">'Sweden'</span><span class="p">,</span> <span class="mi">2</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s1">'SK Telecom T1'</span><span class="p">,</span> <span class="s1">'South Korea'</span><span class="p">,</span> <span class="mi">3</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="s1">'Team Liquid'</span><span class="p">,</span> <span class="s1">'USA'</span><span class="p">,</span> <span class="mi">4</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">5</span><span class="p">,</span> <span class="s1">'G2 Esports'</span><span class="p">,</span> <span class="s1">'Spain'</span><span class="p">,</span> <span class="mi">5</span><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span><span class="line"><span class="cl"><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">players</span> <span class="p">(</span>
</span></span><span class="line"><span class="cl"><span class="n">player_id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">player_name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">team_id</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="k">role</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="n">salary</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">team_id</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">teams</span><span class="p">(</span><span class="n">team_id</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span><span class="line"><span class="cl"><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">players</span> <span class="p">(</span><span class="n">player_id</span><span class="p">,</span> <span class="n">player_name</span><span class="p">,</span> <span class="n">team_id</span><span class="p">,</span> <span class="k">role</span><span class="p">,</span> <span class="n">salary</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="k">VALUES</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'Shroud'</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'Rifler'</span><span class="p">,</span> <span class="mi">100000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'JW'</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'AWP'</span><span class="p">,</span> <span class="mi">90000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s1">'Faker'</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'Mid laner'</span><span class="p">,</span> <span class="mi">120000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="s1">'Stewie2k'</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'Rifler'</span><span class="p">,</span> <span class="mi">95000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">5</span><span class="p">,</span> <span class="s1">'Perkz'</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="s1">'Mid laner'</span><span class="p">,</span> <span class="mi">110000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">6</span><span class="p">,</span> <span class="s1">'Castle09'</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'AWP'</span><span class="p">,</span> <span class="mi">120000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="s1">'Pike'</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'Mid Laner'</span><span class="p">,</span> <span class="mi">70000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">8</span><span class="p">,</span> <span class="s1">'Daron'</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'Rifler'</span><span class="p">,</span> <span class="mi">125000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">9</span><span class="p">,</span> <span class="s1">'Felix'</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'Mid Laner'</span><span class="p">,</span> <span class="mi">95000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">10</span><span class="p">,</span> <span class="s1">'Stadz'</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="s1">'Rifler'</span><span class="p">,</span> <span class="mi">98000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">11</span><span class="p">,</span> <span class="s1">'KL34'</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'Mid Laner'</span><span class="p">,</span> <span class="mi">83000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">12</span><span class="p">,</span> <span class="s1">'ForceZ'</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'Rifler'</span><span class="p">,</span> <span class="mi">130000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">13</span><span class="p">,</span> <span class="s1">'Joker'</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'AWP'</span><span class="p">,</span> <span class="mi">128000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">14</span><span class="p">,</span> <span class="s1">'Hari'</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'AWP'</span><span class="p">,</span> <span class="mi">90000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">15</span><span class="p">,</span> <span class="s1">'Wringer'</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="s1">'Mid laner'</span><span class="p">,</span> <span class="mi">105000</span><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span><span class="line"><span class="cl"><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">matches</span> <span class="p">(</span>
</span></span><span class="line"><span class="cl"><span class="n">match_id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">team1_id</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">team2_id</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">match_date</span> <span class="nb">DATE</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">winner_id</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">score_team1</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">score_team2</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">team1_id</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">teams</span><span class="p">(</span><span class="n">team_id</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">team2_id</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">teams</span><span class="p">(</span><span class="n">team_id</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">winner_id</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">teams</span><span class="p">(</span><span class="n">team_id</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span><span class="line"><span class="cl"><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">matches</span> <span class="p">(</span><span class="n">match_id</span><span class="p">,</span> <span class="n">team1_id</span><span class="p">,</span> <span class="n">team2_id</span><span class="p">,</span> <span class="n">match_date</span><span class="p">,</span> <span class="n">winner_id</span><span class="p">,</span> <span class="n">score_team1</span><span class="p">,</span> <span class="n">score_team2</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="k">VALUES</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'2022-01-01'</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">16</span><span class="p">,</span> <span class="mi">14</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="s1">'2022-02-01'</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">14</span><span class="p">,</span> <span class="mi">9</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'2022-03-01'</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">17</span><span class="p">,</span> <span class="mi">13</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="s1">'2022-04-01'</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">13</span><span class="p">,</span> <span class="mi">12</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">5</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'2022-05-01'</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">16</span><span class="p">,</span> <span class="mi">10</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">6</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'2022-02-01'</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">13</span><span class="p">,</span> <span class="mi">17</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'2022-03-01'</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">12</span><span class="p">,</span> <span class="mi">9</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">8</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'2022-04-01'</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">11</span><span class="p">,</span> <span class="mi">15</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">9</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'2022-05-01'</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">9</span><span class="p">,</span> <span class="mi">10</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">10</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="s1">'2022-01-01'</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="mi">13</span><span class="p">,</span> <span class="mi">10</span><span class="p">);</span>
</span></span>

Entity Relationship Diagram

Questions

Click here to follow along and answer the questions:

  1. What are the names of the players whose salary is greater than 100,000?
    1
    2
    3
    4
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">p</span><span class="p">.</span><span class="n">player_name</span><span class="p">,</span> <span class="n">p</span><span class="p">.</span><span class="n">salary</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">players</span> <span class="k">AS</span> <span class="n">p</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">p</span><span class="p">.</span><span class="n">salary</span> <span class="o">&gt;</span> <span class="mi">100000</span><span class="p">;</span>
    </span></span>

  2. What is the team name of the player with player_id = 3?
    1
    2
    3
    4
    5
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">p</span><span class="p">.</span><span class="n">player_id</span><span class="p">,</span> <span class="n">t</span><span class="p">.</span><span class="n">team_name</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">teams</span> <span class="k">AS</span> <span class="n">t</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">players</span> <span class="k">AS</span> <span class="n">p</span> <span class="k">ON</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span> <span class="o">=</span> <span class="n">p</span><span class="p">.</span><span class="n">team_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">p</span><span class="p">.</span><span class="n">player_id</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span>
    </span></span>

  3. What is the total number of players in each team?
    1
    2
    3
    4
    5
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span><span class="p">,</span> <span class="n">t</span><span class="p">.</span><span class="n">team_name</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">p</span><span class="p">.</span><span class="n">player_name</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total_players</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">teams</span> <span class="k">AS</span> <span class="n">t</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">players</span> <span class="k">AS</span> <span class="n">p</span> <span class="k">ON</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span> <span class="o">=</span> <span class="n">p</span><span class="p">.</span><span class="n">team_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span><span class="p">;</span>
    </span></span>

  4. What is the team name and captain name of the team with team_id = 2?
    1
    2
    3
    4
    5
    6
    7
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span><span class="p">,</span> <span class="n">t</span><span class="p">.</span><span class="n">team_name</span><span class="p">,</span> <span class="n">p</span><span class="p">.</span><span class="n">player_name</span> <span class="k">AS</span> <span class="n">captain_name</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">teams</span> <span class="k">AS</span> <span class="n">t</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">players</span> <span class="k">AS</span> <span class="n">p</span> <span class="k">ON</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span> <span class="o">=</span> <span class="n">p</span><span class="p">.</span><span class="n">team_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">p</span><span class="p">.</span><span class="n">team_id</span> <span class="o">=</span> <span class="mi">2</span>
    </span></span><span class="line"><span class="cl"><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">p</span><span class="p">.</span><span class="n">salary</span> <span class="k">DESC</span>
    </span></span><span class="line"><span class="cl"><span class="k">LIMIT</span> <span class="mi">1</span><span class="p">;</span>
    </span></span>

  5. What are the player names and their roles in the team with team_id = 1?
    1
    2
    3
    4
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">player_name</span><span class="p">,</span> <span class="k">role</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">players</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">team_id</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
    </span></span>

  6. What are the team names and the number of matches they have won?
    1
    2
    3
    4
    5
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">t</span><span class="p">.</span><span class="n">team_name</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">m</span><span class="p">.</span><span class="n">winner_id</span><span class="p">)</span> <span class="k">AS</span> <span class="n">matches_won</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">teams</span> <span class="k">AS</span> <span class="n">t</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">matches</span> <span class="k">AS</span> <span class="n">m</span> <span class="k">ON</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span> <span class="o">=</span> <span class="n">m</span><span class="p">.</span><span class="n">winner_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">m</span><span class="p">.</span><span class="n">winner_id</span><span class="p">;</span>
    </span></span>

  7. What is the average salary of players in the teams with country ‘USA’?
    1
    2
    3
    4
    5
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">t</span><span class="p">.</span><span class="n">country</span><span class="p">,</span> <span class="n">ROUND</span><span class="p">(</span><span class="k">AVG</span><span class="p">(</span><span class="n">p</span><span class="p">.</span><span class="n">salary</span><span class="p">))</span> <span class="k">AS</span> <span class="n">players_avg_salary</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">players</span> <span class="k">AS</span> <span class="n">p</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">teams</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ON</span> <span class="n">p</span><span class="p">.</span><span class="n">team_id</span> <span class="o">=</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">t</span><span class="p">.</span><span class="n">country</span> <span class="o">=</span> <span class="s1">'USA'</span><span class="p">;</span>
    </span></span>

  8. Which team won the most matches?
    1
    2
    3
    4
    5
    6
    7
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">t</span><span class="p">.</span><span class="n">team_name</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">m</span><span class="p">.</span><span class="n">winner_id</span><span class="p">)</span> <span class="k">AS</span> <span class="n">matches_won</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">teams</span> <span class="k">AS</span> <span class="n">t</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">matches</span> <span class="k">AS</span> <span class="n">m</span> <span class="k">ON</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span> <span class="o">=</span> <span class="n">m</span><span class="p">.</span><span class="n">winner_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">m</span><span class="p">.</span><span class="n">winner_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">matches_won</span> <span class="k">DESC</span>
    </span></span><span class="line"><span class="cl"><span class="k">LIMIT</span> <span class="mi">1</span><span class="p">;</span>
    </span></span>

  9. What are the team names and the number of players in each team whose salary is greater than 100,000?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">  <span class="n">t</span><span class="p">.</span><span class="n">team_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">  <span class="n">t</span><span class="p">.</span><span class="n">team_name</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">  <span class="k">COUNT</span><span class="p">(</span><span class="n">p</span><span class="p">.</span><span class="n">player_name</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total_players</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">  <span class="k">SUM</span><span class="p">(</span><span class="n">p</span><span class="p">.</span><span class="n">salary</span><span class="p">)</span> <span class="k">AS</span> <span class="n">sum_salary</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">players</span> <span class="k">AS</span> <span class="n">p</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">teams</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ON</span> <span class="n">p</span><span class="p">.</span><span class="n">team_id</span> <span class="o">=</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">p</span><span class="p">.</span><span class="n">salary</span> <span class="o">&gt;</span> <span class="mi">100000</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">t</span><span class="p">.</span><span class="n">team_id</span><span class="p">;</span>
    </span></span>

  10. What is the date and the score of the match with match_id = 3?
    1
    2
    3
    4
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="n">match_date</span><span class="p">,</span> <span class="n">score_team1</span><span class="p">,</span> <span class="n">score_team2</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">matches</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">match_id</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span>
    </span></span>

Conclusion

As the curtain falls on this epic tournament, the SQL queries provided a window into the dynamics of the competition, answering crucial questions such as the names of players with salaries exceeding $100,000, team details for specific players, overall team statistics, and the outcomes of pivotal matches. This voyage of data analysis has not only illuminated the intricacies of the tournament but has also set the stage for informed decisions and a deeper understanding of the eSports landscape. Let the analysis stand as a testament to the power of data in uncovering the narratives within the realm of competitive gaming.

Adopted form Steel Data.

Download

Cta

Let’s Work Together

Contact Me