The following tables form part of a database held in a relational database management system for a football club, which has many teams in different leagues. The aim of the database is to record its teams' progress in a competition. The database consists of the following tables:
PLAYER (PlayerNo, Name, Birth_Date, Age,Street_Address, Town, Phone, TeamNo).
TEAM (TeamNo,Division)
MATCH (MatchNo, TeamNo, PlayerNo)
RESULT(MatchNo, Win_TeamNo).
PAYMENT (PaymentNo, PlayerNo, Payment_Date, Amount).
CONTRACT (PlayerNo, Begin_Date, End_Date, Position).
Assumption: A player has only one contract with the team and plays for only one team. Player's contract is not renewed once ended.
Using SQL and TransSQL, formulate the following:
(a) The maximum and minimum payment amount of players.
(b) Get the player number and name of players who are less than 30 years old and who live in Manchester. The names should be displayed in alphabetical order.
(c) The number of wins of each team for each division
(d) A procedure "getTotalAmountPaid" which calculates the total amount paid to a player. The procedure takes as input the payment number and displays the player number, player name and total amount paid. For example, assume that the total amount paid for player No10 is £1000000, the procedure should display the following:
"Total Amount paid for Player 10 is £1000000"
(e) A table-valued function "getPlayer" that returns the player number,player name, team number, division, date contract began,date it ended and position of the player. The function takes as input the player number.