Description

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.
• [0 pts] 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 “Beşiktaş”. “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.

IMPORTANT NOTES

• Write the following sentence in a text file: “We hereby swear that the work done on this project is totally our own; and on our honor, we have neither given nor received any unauthorized and/or inappropriate assistance for this project. We understand that by the school code, violation of these principles will lead to a zero grade and is subject to harsh discipline issues.” Rename it as “we_swear.txt” and include this file in the zip submission file.