Your task in this project is to write the following queries in SQL and make sure they run in the PostgreSQL database. If you make any assumptions, clearly state them as comments in your submitted file.

• coaches_season, each tuple of which describes the performance of one coach in one season;
• teams, each tuple of which gives the basic information of a team;
• players, each tuple of which gives the basic information of one player;
• player_rs, each tuple of which gives the detailed performance of one player in one regular season;
• player_rs_career, each tuple of which gives the detailed regular-season performance of one player in his career;
• draft, each tuple of which shows the information of an NBA draft.

1. Find all the coaches who have coached less than TWO teams. List their first names, last names and cids.

2. Find all the players who played in a Miami N league team and a New York A league team (this does not have to happen in the same season). List their first names and ilkids.

3. Find those who happened to be a coach and a player in the same team in the same season. List their first names, last names, the team where this happened, and the year(s) when this happened.

4. Find the average height (in centimeters) of each team in season 2002. Print the team name, the coach name and the average height value (in centimeters), and sort the results by the average height.

5. Find the coach(es) (first name, last name, and cid) who have coached the largest number of players in year 2000.

6. Find the coaches who coached in ALL leagues. List their first names, last names and cids.

7. Find those who happened to be a coach and a player in the same season, but in different teams. List their first names, last names, and the season and the teams this happened. Order the results by ascending season.

8. Find the players who have scored more points than Michael Jordan did in a season. Print out the first name, last name, season, and how many more points they scored than Michael.

9. Find the third most successful coach in regular seasons in history, print his first name and last name, and his level of success, which is defined as season_win /(season_win + season_loss). Note that you have to count in all seasons a coach attended to calculate this value.

10. List the top 3 schools that sent the largest number of drafts to NBA. List the name of each school and the number of drafts sent. Order the results by number of drafts (hint: use "order by" to sort the results and 'limit xxx' to limit the number of rows returned);

Note that the data is not perfectly formatted for our use. For that, you have to try some other techniques that are not a part of the SQL, but are supported by PostgreSQL. For example, the ID of the same player (e.g., ILKID) can be shown as all upper case in one table (e.g., players) but in a mixture of upper and lower cases in another table (e.g., draft). To find more matches, you can use the 'LOWER' or 'UPPER' function to transform a string to all lower or upper case and then do the match.