sqldf lets you ask SQL-style questions of an R data frame. Call sqldf() with a query string, then inspect the returned rows in R. It is especially handy for a quick lookup, a small subset, or a simple aggregate when summary() does not reveal the records you need. section 2.5.2
A query typically names columns with SELECT, identifies the data frame with FROM, and narrows rows with WHERE. SQL aggregates such as MIN, MAX, COUNT, AVG, and MEDIAN can summarize a selected group; ORDER BY can arrange the result. Conditions can be combined with AND. section 2.5.2 section 2.5.3 section 12.4
Find high-value draft selections
To list players from the 2003 draft with at least 75 win shares, sorted from highest to lowest, query the draft data frame: sqldf("SELECT Player, Pk, WS FROM draft WHERE WS >= 75 AND Year == 2003 ORDER BY WS DESC"). The result identifies five players, led by LeBron James. section 2.5.3
Use sqldf for focused retrieval and integrity checks: inspect records flagged as possible duplicates, compare rows before and after a filtering step, or count retained observations. For creating a reusable object for additional analysis, dplyr is presented as the stronger choice; equivalent filters use filter() and & rather than SQL WHERE and AND. section 12.3 section 12.4 section 13.3
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
These short queries retrieve the minimum and maximum win shares, games, and minutes played, together with the associated player and draft details. section 2.5.2
