Based on the following entities and business rules:

A list of SQL statement that you used to: a. Create all your tables b. Load data in your tables You are expected to have enough data to perform various information retrieval queries. c. Retrieve information from your table. - As stated earlier, you are expected to use a minimum of 5 data retrieval queries (SELECT). There is no specific requirement for those last queries, but you are expected to use some of the various SQL elements covered in class (condition, sorting, group, multi-table queries). - For each of those queries, indicate the purpose of the queries. (Example: We want to list the name of all the customers who bought shoes; We want a list of all the transfer students) - For each of those queries display the results returned. Depending on the DBMS you use, you may have a table screenshot or a text file listing all the results.

1. Team: The teams identity and where they play, as well as the level of the team.

• TeamID

1. Unique Integer, not null
2. Represents unique ID for teams and is the Primary Key

• TeamName

1. Text
2. Represents the name of the team with a specific Primary Key

• League

1. Text
2. Represents the League team is in with a specific Primary Key

• StadiumName

1. Text
2. Represents the name of the stadium of a team with a specific Primary Key

• StadiumCapacity

1. Must be a positive integer
2. Represents the number of people the stadium of a team can hold with a specific Primary Key

1. Players The information of the players within a team, and the special qualities they have.

• PlayerID

1. Unique Integer, not null
2. Primary Key, and the unique ID given to a specific Player

• FirstName

1. Text
2. First Name of a player with a specific Primary Key

• LastName

1. Text
2. Last Name of a player with a specific Primary Key

• Phone

1. Char(20)
2. Phone number of a play with a specific Primary Key

• Email

1. Char(20)
2. Email address of a player with a valid specific Primary Key

• Position (forward, midfielder, defender, goalkeeper)

1. Must be a valid position ( Forward, Midfielder, Defender, or GoalKeeper)
2. Represents a certain position of a player with a specific Primary Key

• PreferredFoot (Left, Right)

1. Must be a valid selection: Left or Right
2. Determines what Foot is dominant with the player with a specific Primary Key

• Height

1. Must be a positive integer
2. Height of a player with a specific Primary Key

• Weight

1. Must be a positive integer
2. Weight of a player with a specific Primary Key

1. Coach: Information on the person who is in charge of the team during games, and how much they are paid.

• CoachID

1. Unique Integer
2. Unique ID of a specific coach

• CoachName

1. Text
2. Name of a coach with a specific Primary Key

• Phone

1. Char(20)
2. Phone number for a coach with a specific Primary Key

• Email

1. Char(30)
2. Email address for a coach with a specific Primary Key

• ContractLength

1. Must be a positive integer and greater than zero
2. Scope of time within a paid contract of a coach with a specific Primary Key

• HireDate

1. Must be a valid date
2. Date a coach with a specific Primary Key was hired

• CoachingWins

1. Positive Integer
2. Wins accumulated by a coach with a specific Primary Key

• CoachingLosses

1. Positive Integer
2. Losses accumulated by coach with specific Primary Key

1. Matches: Tracking the data of games specifically who played, when was it played, where was it played, and what happened.

• MatchID

1. Unique Integer, not null
2. Unique ID of a specific match

• Date

1. Must be a valid date
2. Date of a match with a specific Primary Key

• Location

1. Text
2. Location of a match with a specific Primary Key

• Opponent

1. Text
2. Name of the opponent played against in the match with a specific Primary Key

• HomeTeamGoals

1. Must have a positive integer, must be greater than or equal to zero, cannot be null
2. Number of goals the home team has scored in a match with a specific Primary Key

• AwayTeamGoals

1. Must have a positive integer, must be greater than or equal to zero, cannot be null
2. Number of goals an away team has scored in a match with a specific Primary Key

1. Staff: Keeps track of the staff teams have and what job they have with the team.

• StaffID

1. Unique Integer, not null
2. Unique ID of specific staff members

• First Name

1. Text
2. First Name of a staff member with a specific Primary Key

• Last Name

1. Text
2. Last Name of a staff member with a specific Primary Key

• Phone

1. Char(20)
2. Phone Number of staff member with specific Primary Key

• Role (AssistantCoach, FitnessTrainer, Scout)

1. Must be a valid role: AssistantCoach, FitnessTrainer, Scout
2. Represents the role of a staff member with a specific Primary Key.

• DateHired

1. Must be a valid date
2. Date a staff member with a specific Primary key was hired

• Salary

1. Positive Integer
2. Salary of a staff member with a specific Primary Key

1. Sponsors: Keeps track of the companies that are sponsoring the team and how long they are spending.

• CompanyID

1. Unique Integer, not null
2. Unique ID of the company sponsoring the team

• CompanyName

1. Text
2. Name of the company sponsoring with a unique Primary Key

• Industry

1. Text
2. The industry of the company sponsoring with a unique Primary Key

• SponsorshipAmount

1. Positive Integer
2. Amount of money a company with a unique Primary Key is sponsoring with

• SponsorshipContractLength

1. Positive Integer, Must be greater than 0
2. Amount of years left for a company with a unique Primary Key has agreed to sponsor

• AssetsSponsored (stadium, jersey, training ground)

1. Must be valid selection: Stadium, Jersey, Training Ground
2. The specific asset a company with a unique Primary Key is sponsoring

Business Rules:

Each team must have eleven or more players.

Each team must have exactly one coach.

Each team must have one or more staff.

Each team must have exactly 24 matches (a season).

Each team may have one or several sponsors.

Each player must have exactly one team.

Each coach must have exactly one team.

Each staff must be assigned to exactly one team.

Each match must be assigned to exactly two teams.

Each sponsor may have one or several teams.