GEOG 868
Spatial Database Management

Project 1: Writing SQL SELECT Queries

PrintPrint

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.

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 only 3 digits appear after the decimal point (e.g., .275).
  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 years each player played for each of his teams.
  5. List the players based on the number of home runs hit in their rookie seasons (from high to low).
  6. Display the names (only the names, with no duplicates) of players who played in New York (any team beginning with 'NY').
  7. Rank the players by the number of seasons played.
  8. Rank the players by their career batting average (career hits divided by career at-bats).
  9. Rank the players by their number of seasons with 100 or more RBI.
  10. Rank the players based on the number of years between their first and last 40+ HR season.

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

Deliverables

This project is one week in length. Please refer to the Calendar in Canvas for the due date.

  1. Upload your Access database (.accdb file) containing your solutions to the 10 query exercises above to the Project 1 Drop Box in Canvas. (100 of 100 points)
  2. Complete the Lesson 1 quiz.