The team_game relationship stores the information of two teams (home team and away team) for each game. h_id and a_id indicate the id of home team and away team respectively.
computer science
Description
The team_game relationship stores the information of two teams (home team and away team) for each game. h_id and a_id indicate the id of home team and away team respectively. h_points and a_points indicate points that home team and away team scored respectively in this game. The player_game relationship stores the information of players for each game. team_id is the id of the team that the player played for in the game. A player might transfer to another team and played for different teams in different games. points indicates the points that the player scored in the game. points can be null, which indicates the player enrolled but not played in the game. All other relations should be self-explanatory.
Queries:
(3) List players who scored more than 10 points in every game he played and who has played at
least 10 games. The output format should be (id, name) and ordered by name in alphabetical
order. Hint: A player played a game if his score is non-null.
(5) We define partners as a pair of players who played (i.e. two players have non-null points) as
teammates in a game and partner_points as the sum of points that two player scored in the
game. List parters who played in at least 30 games together in season 2017 and the average
partner_points they scored in those games are greater than 40. The output format should be
(player1_id, player2_id, average_parter_points), where player1_id < player2_id. Sort your
output by average parter_points from highest to lowest.
(8) What is the top 10 biggest margin of victory? Output the winning team name, losing team
name, winning team points, losing team points, and the win margin of the games. In case of tie
in the margin, sort the result by winning team points. The format should be (win_team_name,
lose_team_name, win_team_points, lose_team_points, win_margin). Sort your results by
win_margin from highest to lowest, then by win_team_points from highest to lowest.
