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. 


Related Questions in computer science category


Disclaimer
The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.