dbfile <- "/Users/hcorrada/Teaching/CMSC320_Spring2018/materials/lecture-notes/data/lahman2016.sqlite"
db <- DBI::dbConnect(RSQLite::SQLite(), dbfile)

We build the query step by step (starting at the bottom…)

with
  in_hof(playerID) as (
    select distinct playerID
    from HallOfFame
    where inducted = "Y"
  ),
  from_pr_not_in_hof(playerID, nameFirst, nameLast) as (
    select m.playerID, nameFirst, nameLast
    from Master as m
      left join in_hof as hof
        on m.playerID = hof.playerID
    where birthCountry = "P.R." 
      and hof.playerID is NULL
  )
select b.playerID, sum(H) as total_hits, 
  nameFirst, nameLast
from from_pr_not_in_hof as t
  join Batting as b
    on t.playerID = b.playerID
group by b.playerID
order by total_hits desc
limit 10
Displaying records 1 - 10
b.playerID total_hits nameFirst nameLast
beltrca01 2617 Carlos Beltran
willibe02 2336 Bernie Williams
cruzjo01 2251 Jose Cruz
sierrru01 2152 Ruben Sierra
delgaca01 2038 Carlos Delgado
gonzaju03 1936 Juan Gonzalez
santibe01 1830 Benito Santiago
powervi01 1716 Vic Power
posadjo01 1664 Jorge Posada
lowelmi01 1619 Mike Lowell
select m.playerID, sum(b.H) as total_hits, nameFirst, nameLast
from Master as m
  left join (select distinct playerID
      from HallOfFame
      where inducted = "Y") as hof
    on m.playerID = hof.playerID
  join Batting as b
    on m.playerID = b.playerID
where birthCountry = "P.R."
  and hof.playerID is NULL
group by m.playerID
order by total_hits desc
limit 10
Displaying records 1 - 10
playerID total_hits nameFirst nameLast
beltrca01 2617 Carlos Beltran
willibe02 2336 Bernie Williams
cruzjo01 2251 Jose Cruz
sierrru01 2152 Ruben Sierra
delgaca01 2038 Carlos Delgado
gonzaju03 1936 Juan Gonzalez
santibe01 1830 Benito Santiago
powervi01 1716 Vic Power
posadjo01 1664 Jorge Posada
lowelmi01 1619 Mike Lowell
select m.playerID, sum(b.H) as total_hits, nameFirst, nameLast
from Master as m
  left join (select distinct playerID
      from HallOfFame
      where inducted = "Y") as hof
    on m.playerID = hof.playerID
  join Batting as b
    on m.playerID = b.playerID
where birthCountry = "P.R."
  and hof.playerID is NULL
group by m.playerID
Displaying records 1 - 10
playerID total_hits nameFirst nameLast
agostju01 2 Juan Agosto
aguaylu01 260 Luis Aguayo
albaljo01 0 Jonathan Albaladejo
alberjo01 0 Jose Alberro
alcarlu01 70 Luis Alcaraz
alicelu01 1031 Luis Alicea
alomasa01 1168 Sandy Alomar
alomasa02 1236 Sandy Alomar
alvarlu01 248 Luis Alvarado
alvaror01 8 Orlando Alvarez
select m.playerID, b.H, nameFirst, nameLast
from Master as m
  left join (select distinct playerID
      from HallOfFame
      where inducted = "Y") as hof
    on m.playerID = hof.playerID
  join Batting as b
    on m.playerID = b.playerID
where birthCountry = "P.R."
  and hof.playerID is NULL
Displaying records 1 - 10
playerID H nameFirst nameLast
agostju01 0 Juan Agosto
agostju01 0 Juan Agosto
agostju01 0 Juan Agosto
agostju01 0 Juan Agosto
agostju01 0 Juan Agosto
agostju01 0 Juan Agosto
agostju01 0 Juan Agosto
agostju01 0 Juan Agosto
agostju01 0 Juan Agosto
agostju01 1 Juan Agosto
select m.playerID, nameFirst, nameLast
from Master as m
  left join (select distinct playerID
      from HallOfFame
      where inducted = "Y") as hof
    on m.playerID = hof.playerID
where birthCountry = "P.R."
  and hof.playerID is NULL
Displaying records 1 - 10
playerID nameFirst nameLast
agostju01 Juan Agosto
aguaylu01 Luis Aguayo
albaljo01 Jonathan Albaladejo
alberjo01 Jose Alberro
alcarlu01 Luis Alcaraz
alicelu01 Luis Alicea
alomasa01 Sandy Alomar
alomasa02 Sandy Alomar
alvarlu01 Luis Alvarado
alvaror01 Orlando Alvarez
select distinct playerID
from HallOfFame
where inducted = "Y"
Displaying records 1 - 10
playerID
aaronha01
alexape01
alomaro01
alstowa01
andersp01
ansonca01
aparilu01
applilu01
ashburi01
averiea01
select playerID, nameFirst, nameLast
from Master
where birthCountry = "P.R."
Displaying records 1 - 10
playerID nameFirst nameLast
agostju01 Juan Agosto
aguaylu01 Luis Aguayo
albaljo01 Jonathan Albaladejo
alberjo01 Jose Alberro
alcarlu01 Luis Alcaraz
alicelu01 Luis Alicea
alomaro01 Roberto Alomar
alomasa01 Sandy Alomar
alomasa02 Sandy Alomar
alvarlu01 Luis Alvarado
DBI::dbDisconnect(db)