[Solved] CSE3055 Homework #3

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.

• Using the backup file bak accompanied with this homework, restore the database TurkishSuperLeague. Write the following SQL queries, IN A SINGLE STATEMENT, using this database. For each of the following query; save your SQL statements in a text file and take a screenshot of both your SQL query and output of the query on MS SQL Server.

• Update the field Age for all players.

• List the younger players whose first name does not contain nec and play in Beikta. Younger players are the ones whose ages are less than the average age of all players. Retrieve PlayerID, FirstName + + LastName.

• Update all City values of the table Team as: City + #p + Number of players + #g + Number of goals forward (e.g. stanbul #p25 #g74). Do not forget to consider own goals in your calculations.

• List the top 10 top scorers. Retrieve playerID, first name, last name, number of goals scored, number of matches that player did not score a goal, average number of goals per scored matches.

• 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.