Consider the Turkish Super League database accompanied with this homework.
Player (PlayerID: int, FirstName: nvarchar(25), LastName: nvarchar(25), Nationality: varchar(25), Birthdate: smalldatetime, Age: smallint, Position: varchar(25))
Team (TeamID: int, Name: nvarchar(50), City: nvarchar(25))
PlayerTeam (PlayerID: int, TeamID: int, Season: varchar(5))
Match (MatchID: int, HomeTeamID: int, VisitingTeamID: int, DateOfMatch: smalldatetime, Week: tinyint)
Goals (MatchID: int, PlayerID: int, IsOwnGoal: bit, Minute: tinyint)
Notes:
• Table Match stores data only for season 2013-2014.
• Table Goals stores data only for season 2013-2014.
1) [100 pts] Implement a stored procedure sp_GetStandingsUpToDate (aDate) such that exec sp_GetStandingsUpToDate (aDate) computes and shows the standings table, same as in the
following figure, up to the date aDate (inclusive).
Calling with any invalid date causes an error with a message “Invalid date!” All transactions that have been done will be rolled back and stop doing further operations.
1/2
Assoc.Prof.Dr. Mustafa AĞAOĞLU
Notes:
• Invalid date: any date not between 2013-08-16 and 2014-07-31.
• The order of the table: Firstly, Pts; secondly, GT; thirdly, GF.
• Pos will be automatically generated according to the order. Study on ranks.
• GP (Games Played): the number of matches that a team has played.
• W (Wins): the number of wins of a team in all games that have been played.
• T (Ties/Deuce): the number of ties of a team in all games that have been played.
• L (Losts): the number of losts of a team in all games that have been played.
• GF (Goals For/Forward): the number of goals scored of a team in all games that have been played, including own goals of the other team.
• GA (Goals Against): the number of goals scored by the other teams in all games that have been played, including own goals of the team.
• GD (Goals Difference): GF-GA.
• Pts (Points): the points accumulated by a team. For each win, a team gets 3 points; for each tie, a team gets 1 point; and, for each lost, a team gets 0 points.
