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
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 |
- Final query: Ten players from PR not in HOF with the most career hits.
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
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 |
- Total number of career hits for players from PR not in HOF
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
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 |
- Add hits per season to table of players from PR _not_in HOF
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
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 |
- Players from PR not in HOF
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
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
aaronha01 |
alexape01 |
alomaro01 |
alstowa01 |
andersp01 |
ansonca01 |
aparilu01 |
applilu01 |
ashburi01 |
averiea01 |
select playerID, nameFirst, nameLast
from Master
where birthCountry = "P.R."
Displaying records 1 - 10
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)