A SELECT statement retrieves chosen fields or expressions from a data source. In R, sqldf() makes it practical to ask focused NBA data questions, such as finding the player with the minimum or maximum win shares. section 2.5.2
Use a SELECT statement to:
- Name the variables to return and identify their source with SELECT and FROM. section 2.5.2
- Retrieve one record or a short list, such as salaries for a particular team and season. section 2.5.2 section 12.4
- Filter results with WHERE and combine conditions with AND; dplyr provides a related approach through filter(), but uses different syntax. section 12.4
- Inspect messy data before analysis, including blank teams and seasons with unusually few records. section 12.4
Find one team-season
sqldf("SELECT * FROM gini3 WHERE season_end = 2012 AND team = 'Denver Nuggets'") returns the Denver Nuggets’ 2012 salary records. Applying the same query to gini4 returns only the ranked record retained after preprocessing. section 12.4
sqldf("SELECT min(WS), Player, Tm, Pk, Year FROM draft")
## min(WS) Player Tm Pk Year
## 1 -1.6 Mardy Collins NYK 29 2006
sqldf("SELECT max(WS), Player, Tm, Pk, Year FROM draft")
## max(WS) Player Tm Pk Year
## 1 236.1 LeBron James CLE 1 2003
sqldf("SELECT min(G), Player, Tm, Pk, Year FROM draft")
## min(G) Player Tm Pk Year
## 1 6 Pavel Podkolzin UTA 21 2004
sqldf("SELECT max(G), Player, Tm, Pk, Year FROM draft")
## max(G) Player Tm Pk Year
## 1 1326 Jamal Crawford CLE 8 2000
sqldf("SELECT min(MP), Player, Tm, Pk, Year FROM draft")
## min(MP) Player Tm Pk Year
## 1 4.3 Julius Hodge DEN 20 2005
sqldf("SELECT max(MP), Player, Tm, Pk, Year FROM draft")
## max(MP) Player Tm Pk Year
## 1 38.4 LeBron James CLE 1 2003
sqldf("SELECT min(Age), Player, Tm, Pk, Year FROM draft")
## min(Age) Player Tm Pk Year
## 1 17.249 Andrew Bynum LAL 10 2005
sqldf("SELECT max(Age), Player, Tm, Pk, Year FROM draft")
## max(Age) Player Tm Pk Year
## 1 25.019 Mamadou N'Diaye DEN 26 2000
Compare SELECT queries that retrieve minimum and maximum values alongside player, team, draft pick, and year. section 2.5.2
