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) [0 pts] Using the backup file TurkishSuperLeague_20201128_StudentVersion.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.
2) [10 pts] Update the field Age for all players.
3) [20 pts] List the “younger” players whose first name does not contain “nec” and play in “Beşiktaş”. “Younger” players are the ones whose ages are less than the average age of all players. Retrieve PlayerID, FirstName + ” “ + LastName.
4) [30 pts] 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.
5) [40 pts] 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.
