Player plays in a single team in a given season. However, the player may change the teams

Fantastic news! We've Found the answer you've been seeking!

Question:

Player plays in a single team in a given season. However, the player may change the teams from a season to the next. For a player we keep the name (we want to search based on the last name), the date of birth, the jersey number (which can change when the player changes the team or from a season to another) and the player home city.

A coach coaches one or more teams per seasons, but at most 5. A team is coached by a single coach. For a coach we keep the name (we search based on first and last name), the date of birth, the year starting coaching and home zipcode.

For the new entities in S4, as applicable, the first, last and middle name components are at most 22 characters long. We store Zipcodes-5. Cities are at most 25 characters long.

Organize the persons associated with the league in a hierarchy.

Database Constraints for Step 4

Two players have the jersey number 1 and all the other players have bigger numbers.
Two coaches started to coach in 2019, while all the others earlier.
Have at least two players registered to a team in SPRING 2018 and two in Fall 2018.
Have at least two coaches hired by a club in SPRING 2018 and two in FALL 2018.

Database Questions for Step 5

Your questions must work on any data, you must not assume anything about the data when writing the question.

Count all the players registered for a team in Fall 2018.
Count all the coaches hired by a club in Spring 2018.
List the player(s) that have the smallest jersey number (the full name in a column named player).
List the least experienced coaches (the full name in a column named coach).