Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>cd c:\retrosheet

C:\retrosheet>dir
 Volume in drive C has no label.
 Volume Serial Number is 8723-79C9

 Directory of C:\retrosheet

01/05/2007  09:57 AM    <DIR>          .
01/05/2007  09:57 AM    <DIR>          ..
01/04/2007  10:55 AM     4,461,889,536 retrosheet-sqlite3-20070101.db
01/04/2007  12:01 PM           400,589 sqlite3.exe
               2 File(s)  4,462,290,125 bytes
               2 Dir(s)   9,422,229,504 bytes free

C:\retrosheet>sqlite3 retrosheet-sqlite3-20070101.db
SQLite version 3.3.9
Enter ".help" for instructions
sqlite> .tables
boxscores   events      eventtypes  games       rosters     teams
sqlite> .schema rosters
CREATE TABLE rosters (
    yearid                                      INTEGER,
    retroid                                     CHARACTER VARYING(8),
    lastname                                    CHARACTER VARYING(25),
    firstname                                   CHARACTER VARYING(25),
    bats                                        CHARACTER VARYING(1),
    throws                                      CHARACTER VARYING(1),
    team                                        CHARACTER VARYING(3),
    pos                                         CHARACTER VARYING(5)
);
CREATE INDEX rosters_retroid_idx ON rosters (retroid);
sqlite> select retroid,yearid,firstname,lastname from rosters where lastname = '
Hafner' and yearid = 2006;
retroid|yearid|firstname|lastname
hafnt001|2006|Travis|Hafner

sqlite> select retroid,yearid,firstname,lastname from rosters where lastname = '
Smith' and yearid = 2006;
retroid|yearid|firstname|lastname
smitj001|2006|Jason|Smith
smitm006|2006|Matt|Smith
smitt001|2006|Travis|Smith
smitm006|2006|Matt|Smith
smitm005|2006|Mike|Smith

sqlite> .schema events
CREATE TABLE events (
    gameid                                      CHARACTER VARYING(12),
    visitingteam                                CHARACTER VARYING(3),
    inning                                      INTEGER,
    battingteam                                 INTEGER,
    outs                                        INTEGER,
    balls                                       INTEGER,
    strikes                                     INTEGER,
    pitchsequence                               CHARACTER VARYING(40),
    visscore                                    INTEGER,
    homescore                                   INTEGER,
    batter                                      CHARACTER VARYING(8),
    batterhand                                  CHARACTER VARYING(1),
    resbatter                                   CHARACTER VARYING(8),
    resbatterhand                               CHARACTER VARYING(1),
    pitcher                                     CHARACTER VARYING(8),
    pitcherhand                                 CHARACTER VARYING(1),
    respitcher                                  CHARACTER VARYING(8),
    respitcherhand                              CHARACTER VARYING(1),
    catcher                                     CHARACTER VARYING(8),
    firstbase                                   CHARACTER VARYING(8),
    secondbase                                  CHARACTER VARYING(8),
    thirdbase                                   CHARACTER VARYING(8),
    shortstop                                   CHARACTER VARYING(8),
    leftfield                                   CHARACTER VARYING(8),
    centerfield                                 CHARACTER VARYING(8),
    rightfield                                  CHARACTER VARYING(8),
    firstrunner                                 CHARACTER VARYING(8),
    secondrunner                                CHARACTER VARYING(8),
    thirdrunner                                 CHARACTER VARYING(8),
    eventtext                                   CHARACTER VARYING(100),
    leadoffflag                                 CHARACTER VARYING(1),
    pinchhitflag                                CHARACTER VARYING(1),
    defensiveposition                           CHARACTER VARYING(2),
    lineupposition                              INTEGER,
    eventtype                                   INTEGER,
    battereventflag                             CHARACTER VARYING(1),
    abflag                                      CHARACTER VARYING(1),
    hitvalue                                    INTEGER,
    shflag character                            varying(1),
    sfflag character                            varying(1),
    outsonplay                                  INTEGER,
    doubleplayflag                              CHARACTER VARYING(1),
    tripleplayflag                              CHARACTER VARYING(1),
    rbionplay                                   INTEGER,
    wildpitchflag                               CHARACTER VARYING(1),
    passedballflag                              CHARACTER VARYING(1),
    fieldedby                                   INTEGER,
    battedballtype                              CHARACTER VARYING(1),
    buntflag                                    CHARACTER VARYING(1),
    foulflag                                    CHARACTER VARYING(1),
    hitlocation                                 CHARACTER VARYING(5),
    numerrors                                   INTEGER,
    firsterrorplayer                            CHARACTER VARYING(8),
    firsterrortype                              CHARACTER VARYING(1),
    seconderrorplayer                           CHARACTER VARYING(8),
    seconderrortype                             CHARACTER VARYING(1),
    thirderrorplayer                            CHARACTER VARYING(8),
    thirderrortype                              CHARACTER VARYING(1),
    batterdest                                  INTEGER,
    runneronfirstdest                           INTEGER,
    runneronseconddest                          INTEGER,
    runneronthirddest                           INTEGER,
    playonbatter                                CHARACTER VARYING(8),
    playonrunneronfirst                         CHARACTER VARYING(15),
    playonrunneronsecond                        CHARACTER VARYING(15),
    playonrunneronthird                         CHARACTER VARYING(15),
    sbforrunneronfirstflag                      CHARACTER VARYING(1),
    sbforrunneronsecondflag                     CHARACTER VARYING(1),
    sbforrunneronthirdflag                      CHARACTER VARYING(1),
    csforrunneronfirstflag                      CHARACTER VARYING(1),
    csforrunneronsecondflag                     CHARACTER VARYING(1),
    csforrunneronthirdflag                      CHARACTER VARYING(1),
    poforrunneronfirstflag                      CHARACTER VARYING(1),
    poforrunneronsecondflag                     CHARACTER VARYING(1),
    poforrunneronthirdflag                      CHARACTER VARYING(1),
    responsiblepitcherforrunneratfirst          CHARACTER VARYING(8),
    responsiblepitcherforrunneratsecond         CHARACTER VARYING(8),
    responsiblepitcherforrunneratthird          CHARACTER VARYING(8),
    newgameflag                                 CHARACTER VARYING(1),
    endgameflag                                 CHARACTER VARYING(1),
    pinchrunneronfirst                          CHARACTER VARYING(1),
    pinchrunneronsecond                         CHARACTER VARYING(1),
    pinchrunneronthird                          CHARACTER VARYING(1),
    runnerremovedforpinchrunnerfirst            CHARACTER VARYING(8),
    runnerremovedforpinchrunneronsecond         CHARACTER VARYING(8),
    runnerremovedforpinchrunneronthird          CHARACTER VARYING(8),
    batterremovedforpinchhitter                 CHARACTER VARYING(8),
    positionofbatterremovedforpinchhitter       INTEGER,
    fielderwithfirstputout                      INTEGER,
    fielderwithsecondputout                     INTEGER,
    fielderwiththirdputout                      INTEGER,
    fielderwithfirstassist                      INTEGER,
    fielderwithsecondassist                     INTEGER,
    fielderwiththirdassist                      INTEGER,
    fielderwithfourthassist                     INTEGER,
    fielderwithfifthassist                      INTEGER,
    eventnum INTEGER
);
CREATE INDEX events_batter_idx  ON events (batter);
CREATE INDEX events_gameid_idx  ON events (gameid);
CREATE INDEX events_pitcher_idx ON events (pitcher);

sqlite> select * from eventtypes;
eventtype|eventname
0|Unknown event
1|No event
2|Generic out
3|Strikeout
4|Stolen base
5|Defensive indifference
6|Caught stealing
7|Pickoff error
8|Pickoff
9|Wild pitch
10|Passed ball
11|Balk
12|Other advance
13|Foul error
14|Walk
15|Intentional walk
16|Hit by
17|Interference
18|Error
19|Fielder's choice
20|Single
21|Double
22|Triple
23|Home run
24|Missing play

sqlite> select gameid,pitcher,batter,eventtext,pitchsequence from events where b
atter = 'hafnt001' and eventtype = 23 and rbionplay = 4;
gameid|pitcher|batter|eventtext|pitchsequence
CLE200404120|lohsk001|hafnt001|HR/9/F.3-H;2-H;1-H|BX
BOS200506280|foulk001|hafnt001|HR/9/L.3-H;2-H;1-H|CSX
CIN200607010|maysj001|hafnt001|HR/8/F.3-H;2-H;1-H|.*BFX
CLE200605010|mccab001|hafnt001|HR/9/F.3-H;2-H;1-H|B*BX
CLE200605170|affej001|hafnt001|HR/9/F.3-H(UR);2-H;1-H|X
CLE200606030|donnb001|hafnt001|HR/8/F.3-H(UR);2-H(UR);1-H(UR);B-H(UR)|SBX
CLE200607070|bensk001|hafnt001|HR/9/L.3-H;2-H;1-H|X
CLE200608130|hudsl001|hafnt001|HR/9/F.3-H;2-H;1-H|BSX

sqlite> select gameid,pitcher,batter,eventtext,pitchsequence from events where b
atter = 'hafnt001' and eventtype = 23 and rbionplay = 4 and gameid like '%2006%'
;
gameid|pitcher|batter|eventtext|pitchsequence
CIN200607010|maysj001|hafnt001|HR/8/F.3-H;2-H;1-H|.*BFX
CLE200605010|mccab001|hafnt001|HR/9/F.3-H;2-H;1-H|B*BX
CLE200605170|affej001|hafnt001|HR/9/F.3-H(UR);2-H;1-H|X
CLE200606030|donnb001|hafnt001|HR/8/F.3-H(UR);2-H(UR);1-H(UR);B-H(UR)|SBX
CLE200607070|bensk001|hafnt001|HR/9/L.3-H;2-H;1-H|X
CLE200608130|hudsl001|hafnt001|HR/9/F.3-H;2-H;1-H|BSX

sqlite> select gameid,pitcher,batter,eventtext,pitchsequence from events where e
venttype = 23 and rbionplay = 4 and gameid like '%2006%';
gameid|pitcher|batter|eventtext|pitchsequence
HOU200604240|lidgb001|garcn001|HR/78/F.3-H;2-H;1-H|BBFSX
HOU200605150|bucht001|felip001|HR/7/F.3-H;2-H;1-H|X
HOU200608290|kolbd001|lanej001|HR/7/F.3-H;2-H;1-H|.*BBB.X
HOU200609150|clemr001|burrp001|HR/7/F.3-H;2-H;1-H|FBBCB>X
OAK200604030|zitob001|rodra001|HR/7/F.3-H;2-H;1-H|BCX
OAK200604220|weavj002|swisn001|HR/9/L.3-H;2-H;1-H|BX
OAK200605160|hernf002|melha001|HR/89/F.3-H(UR);2-H(UR);1-H(UR);B-H(UR)|FBBX
OAK200607290|zitob001|glaut001|HR/78/F.3-H;2-H;1-H|BX
OAK200609030|pennh001|johnd004|HR/8/F.3-H;2-H;1-H|X
OAK200609190|lee-c003|kielb001|HR/7/F.3-H;2-H;1-H|X
SFN200606110|worrt002|bautj002|HR/7/F.3-H;2-H;1-H|BCX
SFN200606280|rheij001|durhr001|HR/7/F.3-H;2-H;1-H|BBFBX
CHN200604090|isrij001|barrm003|HR/7/F.3-H;2-H;1-H|C*BFFBX
CHN200604110|ohmaw001|encae001|HR/7/L+.3-H;2-H;1-H|FX
CHN200607160|marss002|floyc001|HR/7/F.3-H(UR);2-H;1-H|BCSX
CHN200607160|novor002|beltc001|HR/78/F.3-H(UR);2-H(UR);1-H(UR);B-H(UR)|BFBBS>X
CHN200607310|priom001|hudso001|HR/8/F.3-H;2-H;1-H|BBCBX
CHN200609060|cappm001|lee-d002|HR/78/L.3-H;2-H;1-H|X
KCA200609150|crucf001|shear001|HR/78/F.3-H;2-H;1-H|FX
TEX200604210|mccls002|menck001|HR/7/F.3-H;2-H;1-H|BFX
TEX200604260|calek001|menck001|HR/7/L.3-H;2-H;1-H|..B*BCCX
TEX200605250|halsb001|barar001|HR/7/F.3-H;2-H;1-H|FFX
TEX200605300|mechg001|wilkb002|HR/9/F.3-H;2-H;1-H|SCBBX
TEX200605310|hernf002|mattg002|HR/9/F.3-H;2-H;1-H|BBCBX
TEX200608160|gregk001|cruzn002|HR/78/F.3-H;2-H;1-H|.CCX
TEX200609020|volqe001|kouzk001|HR/8/F.3-H;2-H;1-H|X
MIN200606100|halaj001|cuddm001|HR/8/F.3-H;2-H;1-H|CSBX
MIN200606130|tavaj001|kubej002|HR/9/F.3-H;2-H;1-H|CBFBFBX
MIN200606140|delcm001|mornj001|HR/7/F.3-H;2-H;1-H|.BBBCX
MIN200606280|pereo001|huntt001|HR/7/F.3-H;2-H;1-H|BSFX
MIN200608010|bakes002|mattg002|HR/9/F+.3-H;2-H;1-H|BBFBFX
CHA200605030|sorir001|credj001|HR/78/F.3-H;2-H;1-H|.X
CHA200605200|hillr001|iguct001|HR/7/F.3-H;2-H;1-H|CBBFX
CHA200606230|petta001|podss001|HR/9/F.3-H;2-H;1-H|C*B*BFFX
CHA200606240|qualc001|credj001|HR/7/F.3-H;2-H;1-H|.X
CHA200606250|lidgb001|iguct001|HR/78/F.3-H;2-H;1-H|BFX
CHA200607060|ortir001|thomj002|HR/8/F.3-H;2-H;1-H|BBCBX
CHA200608160|dohms001|cinta001|HR/9/F.3-H;2-H;1-H|X
CHA200609190|verlj001|piera001|HR/8/F.3-H;2-H;1-H|BSF*BX
CHA200609210|riskd001|belta001|HR/78/F.3-H;2-H;1-H|.X
CHA200609240|pinej001|uribj002|HR/7/F.3-H;2-H;1-H|.FX
BOS200604130|clemm001|wellv001|HR/8/F.3-H;2-H;1-H|FBX
BOS200605070|bensk001|varij001|HR/9/F.3-H;2-H;1-H|B*BX
BOS200606210|hills003|ortid001|HR/78/F.3-H;2-H;1-H|X
BOS200608030|beckj002|choos001|HR/8/F.3-H;2-H;1-H|X
BOS200608130|cabrd001|lowem001|HR/7/F.3-H;2-H;1-H|BBBCX
ATL200605130|cordc001|franj004|HR/7/F+.3-H;2-H;1-H|BX
ATL200607300|jamec002|beltc001|HR/7/F.3-H;2-H;1-H|BCBFBFX
TBA200604120|rleas001|gomej001|HR/7/F.3-H;2-H;1-H(UR)|CBX
TBA200605040|miced001|damoj001|HR/89/F.3-H;2-H(UR);1-H|CX
TBA200607060|camps002|ortid001|HR/78/F.3-H;2-H;1-H|X
TBA200607090|fossc001|giamj001|HR/89/F.3-H;2-H;1-H|FBCFX
TBA200607210|loewa001|cantj001|HR/78/F.3-H;2-H;1-H|BX
TBA200608200|meadb001|peraj001|HR/78/F.3-H;2-H;1-H|FBTX
TBA200609080|shiej002|swisn001|HR/89/F.3-H;2-H;1-H|BX
MIL200604200|adamm001|philb001|HR/78/F.3-H;2-H;1-H|FX
MIL200605190|capej001|batit001|HR/78/F.3-H;2-H;1-H|X
MIL200606040|winkj001|soria001|HR/78/F.3-H;2-H;1-H|.BCBCX
ARI200605020|pereo001|tracc001|HR/9/F.3-H;2-H;1-H|FBX
ARI200605190|batim001|laroa001|HR/9/F.3-H;2-H;1-H|SX
ARI200608050|nippd001|ausmb001|HR/78/F.3-H;2-H;1-H|BX
NYN200606180|loewa001|wrigd002|HR/7/F.3-H;2-H;1-H|X
NYN200607070|limaj001|willd003|HR/9/F.3-H(UR);2-H;1-H|X
NYN200607082|nolar001|valej003|HR/9/F.3-H;2-H;1-H|BSBX
NYN200607210|bucht001|valej003|HR/89/F.3-H;2-H(UR);1-H|CX
NYN200608060|maths001|reyej001|HR/9/F.3-H(UR);2-H(UR);1-H(UR);B-H(UR)|BBX
NYN200608220|mainj001|pujoa001|HR/78/F.3-H;2-H;1-H|BCX
NYN200608220|weavj002|delgc001|HR/9/F.3-H(UR);2-H;1-H|BCB*BX
NYN200608260|pereo002|howar001|HR/89/F.3-H;2-H;1-H|SSX
TOR200604050|frasj002|huntt001|HR/8/L.3-H;2-H;1-H|BBX
TOR200605100|blanj001|overl001|HR/8/F.3-H;2-H;1-H|BFX
TOR200607170|koroj001|wellv001|HR/7/F.3-H;2-H;1-H|BX
TOR200608270|pereo001|molib001|HR/7/F+.3-H;2-H;1-H|X
TOR200609050|accaj001|marta004|HR/8/L.3-H;2-H;1-H|X
PHI200604030|santj001|roles001|HR/7/F.3-H;2-H;1-H|BX
PHI200604180|gearg001|churr001|HR/9/F.3-H;2-H;1-H|FCBFFFX
PHI200607301|sanca004|liebm001|HR/7/L.3-H;2-H;1-H|BX
PHI200609180|marss002|hernj001|HR/78/F.3-H;2-H;1-H|.BC*BFBX
NYA200607020|solea001|rodra001|HR/89/L.3-H;2-H;1-H|CBFX
ANA200605120|livib001|rivej001|HR/7/F.3-H;2-H;1-H|CFX
ANA200606100|lackj001|sexsr001|HR/8/F.3-H;2-H;1-H|CBX
ANA200607150|harvc001|guerv001|HR/8/F.3-H;2-H;1-H|BX
ANA200608040|lackj001|derom001|HR/7/F.3-H;2-H;1-H|BBFX
LAN200607280|corcr001|drewj001|HR/89/F.3-H;2-H;1-H|X
LAN200609190|desse001|bautj002|HR/7/F.3-H;2-H;1-H|B*BX
LAN200609240|vizcl001|garcn001|HR/8/F.3-H;2-H;1-H|BCBX
WAS200606170|rives001|damoj001|HR/9/F.3-H;2-H(UR);1-H|BB+3BCX
SEA200605220|bedae001|sexsr001|HR/8/F.3-H;2-H;1-H|BX
SEA200605230|woodj002|hernr002|HR/7/F.3-H;2-H;1-H|B*BCBX
SEA200606070|sorir001|cuddm001|HR/7/F.3-H;2-H;1-H|CFFFBX
SEA200607250|frute001|mcdoj003|HR/7/F.3-H;2-H;1-H|BX
SEA200608080|mccls002|sexsr001|HR/8/F.3-H;2-H;1-H|BX
SEA200608270|snydk001|ibanr001|HR/9/F.3-H;2-H;1-H|BBX
SEA200609080|bauer001|sexsr001|HR/8/F.3-H;2-H;1-H|.BCBX
SEA200609130|jimec002|zaung001|HR/9/L.3-H;2-H;1-H|BX
COL200604140|kim-s001|utlec001|HR/8/F.3-H;2-H;1-H|X
COL200606070|pereo002|torry001|HR/7/F.3-H;2-H;1-H|FX
COL200607080|fuenb001|tracc001|HR/9/F.3-H;2-H;1-H|CX
COL200608130|marmc001|atkig001|HR/7/F.3-H;2-H;1-H|BB*BCX
COL200608300|foggj001|wrigd002|HR/7/F.3-H;2-H;1-H|BFBX
COL200609100|schrc001|heltt001|HR/9/F.3-H;2-H;1-H|BX
COL200609240|morij001|mccab002|HR/89/F.3-H;2-H;1-H|BCBTBFFX
COL200609240|jamec002|hollm001|HR/8/F.3-H;2-H;1-H|BX
COL200609280|kim-b001|lonej001|HR/7/F.3-H;2-H;1-H|BX
BAL200607280|ray-c001|gloar001|HR/7/F.3-H;2-H;1-H|.BBBCFX
CIN200606100|yan-e001|barrm003|HR/7/F.3-H;2-H;1-H|.BX
CIN200606160|claub001|credj001|HR/7/L.3-H;2-H;1-H|CX
CIN200606300|wickb001|dunna001|HR/9/L+.3-H;2-H;1-H|BX
CIN200607010|maysj001|hafnt001|HR/8/F.3-H;2-H;1-H|.*BFX
CIN200607180|milte001|beltc001|HR/7/F+.3-H;2-H;1-H|SX
CIN200608090|haraa001|edmoj001|HR/7/F.3-H;2-H;1-H(UR)|BS*BX
PIT200604130|pereo002|rossc001|HR/78/F.3-H;2-H;1-H|BCFX
PIT200605180|willd002|bay-j001|HR/78/F.3-H;2-H;1-H|X
SDN200604230|zambv001|gileb002|HR/9/L.3-H;2-H;1-H|BS*B*BF>X
SDN200608170|tascj001|barfj003|HR/7/F.3-H;2-H;1-H|..BTFBX
CLE200604070|lohsk001|blakc001|HR/7/F.3-H;2-H;1-H|X
CLE200604120|gravd001|sexsr001|HR/7/L.3-H;2-H;1-H|X
CLE200604270|beckj002|broub001|HR/9/F.3-H;2-H;1-H|X
CLE200605010|mccab001|hafnt001|HR/9/F.3-H;2-H;1-H|B*BX
CLE200605170|affej001|hafnt001|HR/9/F.3-H(UR);2-H;1-H|X
CLE200606030|donnb001|hafnt001|HR/8/F.3-H(UR);2-H(UR);1-H(UR);B-H(UR)|SBX
CLE200607050|byrdp001|cabrm002|HR/89/F.3-H;2-H;1-H(UR)|BX
CLE200607070|bensk001|hafnt001|HR/9/L.3-H;2-H;1-H|X
CLE200608130|hudsl001|hafnt001|HR/9/F.3-H;2-H;1-H|BSX
CLE200609250|logab001|blakc001|HR/7/F.3-H;2-H;1-H|CX
DET200605200|zumaj001|grifk002|HR/9/F.3-H;2-H;1-H|FBX
DET200605280|seayb001|michj001|HR/7/F.3-H;2-H;1-H|BX
DET200607190|vazqj001|monrc001|HR/7/F.3-H;2-H;1-H|FBFBX
DET200609150|rleas001|ingeb001|HR/7/F.3-H;2-H;1-H|.BX
SLN200606030|muldm001|ramia001|HR/8/F.3-H(UR);2-H;1-H(UR)|BBX
SLN200607170|weavj002|mccab002|HR/9/F.3-H;2-H;1-H|FBFBFFBFX
SLN200608270|howrb001|benng001|HR/7/F.3-H;2-H;1-H|BX

sqlite> .output 2006grandslams.csv
sqlite> select gameid,pitcher,batter,firstrunner,secondrunner,thirdrunner,eventt
ext,pitchsequence from events where eventtype = 23 and rbionplay = 4 and gameid
like '%2006%';
sqlite> .output stdout
sqlite> select count(*) from events where eventtype = 23 and rbionplay = 4 and g
ameid like '%2006%';
132
sqlite> .quit

C:\retrosheet>