Project 1: Writing SQL SELECT Queries

Project 1: Writing SQL SELECT Queries

To demonstrate that you've learned the material from Lesson 1, please build queries in your Access database for each of the problems posed below. Some tips:

• Remember that players who played for multiple teams in the same season (i.e., were traded mid-season) will have a separate row in the STATS table for each team-year combination.
• Even if the requested query does not explicitly ask for player names in the output, please include names to make the output more informative.
• Some of the problems require the use of sub-queries. You will receive equal credit whether you choose to embed your sub-query within the larger query or save your sub-query separately and build your final query upon that saved sub-query.
• You should treat the words 'season' and 'year' in the problems below as synonymous.

Here are the problems to solve:

1. Display all records/fields from the STATS table for players on the San Francisco Giants (SFG).
2. Output the batting average (HITS divided by AB) for each player and season. Format this value so that exactly 3 digits appear after the decimal point.
   
   For example, Babe Ruth had a batting average of .200 in 1914 (2 hits in 10 at-bats).
    
3. Display all records/fields from the STATS table along with the names of the players in the format “Ruth, Babe”.
4. Calculate the number of seasons each player played for each of his teams. (Players who were on two teams in the same season can be counted as having played for both teams.)
   
   For example, Mark McGwire played 12 seasons for the Oakland Athletics and 5 seasons for the St Louis Cardinals (1997 being counted for both teams).
    
5. List the players based on the number of home runs hit in their rookie (first) seasons, from high to low.
   
   For example, Frank Robinson hit 38 home runs in his rookie season of 1956.
    
6. Display the names (only the names, with no duplicates) of players who played in New York (any team beginning with 'NY').
7. Sort the players by the number of seasons played (high to low). (Do not double-count in cases in which a player switched teams mid-season.)
   
   For example, Sammy Sosa played 18 seasons, with 1989, which he split between CHW and TEX counted as 1 season, not 2.
    
8. Sort the players by their career batting average (career hits divided by career at-bats, high to low).
   
   For example, Harmon Killebrew had a career batting average of .256 (2086 hits in 8147 at-bats).
    
9. Sort the players by their number of seasons with 100 or more RBI (high to low).
   
   For example, Hank Aaron had 100 or more RBI in a season 11 times.
    
10. Sort the players based on the number of years between their first and last seasons of 40 or more HR (from most to least).
    
    For example, Willie Mays had his first 40-HR season in 1954 and his last in 1965, a difference of 11 years.
     

Name your queries so that it's easy for me to identify them (e.g., Proj1_1 through Proj1_10).