Homework #4
Consider the Turkish Super League database that has been e-mailed to you.
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)) player_team (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.
- Restore the database TurkishSuperLeague using the file bak that has been e-mailed to you. Write the following SQL queries, in only one statement, using this database. For each of the following query, 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 include 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.
