Challenge 2 - Esports Tournament

The top eSports competitors from across the globe have gathered to battle it out. Here is the data table that I have to analyze

What are the names of the players whose salary is greater than 100,000?

SELECT
	player_name,
	salary
FROM
	Players
WHERE 
	salary > 100000        

2. What is the team name of the player with player_id = 3?

SELECT
	T.team_name,
	P.player_id
FROM 
	Teams AS T
	JOIN Players As P On T.team_id = P.team_id
WHERE
	P.player_id = 3        

3. What is the total number of players in each team?

SELECT
	T.team_name,
	COUNT(P.player_id) As total_players
FROM
	Teams T
	JOIN Players P ON T.team_id = P.team_id
GROUP BY
	team_name        

4. What is the team name and captain name of the team with team_id = 2?

SELECT
	team_name,
	player_name AS captain_name
FROM
	Teams T
	JOIN Players P ON T.captain_id = P.player_id
WHERE
	T.team_id =2        

5. What are the player names and their roles in the team with team_id = 1?

SELECT
	player_name,
	role
FROM
	Teams  T
	JOIN Players P ON T.team_id = P.team_id
WHERE
	T.team_id = 1        

6. What are the team names and the number of matches they have won?

SELECT
	T.team_name,
	count(M.winner_id) AS no_of_matches_won
FROM
	Teams T
	JOIN Matches M ON T.team_id = M.winner_id
GROUP BY
	team_name
ORDER BY
	no_of_matches_won DESC        

7. What is the average salary of players in the teams with country 'USA'?

SELECT
	team_name,
	AVG(Salary) AS avg_salary
FROM
	Teams T
	JOIN Players P ON T.team_id = P.team_id
WHERE
	T.country = 'USA'
GROUP BY
	T.team_name        

8. Which team won the most matches?

SELECT Top 1
	team_name,
	Count(winner_id) As num_match_won
FROM
	Teams T
	JOIN Matches M ON T.team_id = M.winner_id
GROUP BY
	T.team_name
ORDER BY Count(winner_id) DESC        

9. What are the team names and the number of players in each team whose salary is greater than 100,000?

SELECT
	team_name,
	count(player_name) AS num_of_players
FROM
	Teams T
	JOIN Players P ON T.team_id = P.team_id
WHERE
	salary > 100000
GROUP BY
	T.team_name        

10. What is the date and the score of the match with match_id = 3?

SELECT
	match_date,
	(score_team1+score_team2) AS score
FROM
	Matches
WHERE
	match_id =3