Output of Consistency Checks Run with the number of rows in the output. Usually should be zero. See actual queries used below. ---------------------------------------------------------------------- Running 60 queries:0 1 => 0 Unique playerIDs (-q 1 to check) 2 => 0 Unique hofIDs (-q 2 to check) 3 => 0 Unique managerIDs (-q 3 to check) 4 => 0 Make certain the playerIDs are tightly packed (-q 4 to check) 5 => 39 [1 expected] Make certain all playersIDs in Master are in Batting (-q 5 to check) 6 => 0 Make certain all IDs in Batting are in Master (-q 6 to check) 7 => 0 Make certain all IDs in Pitching are in Master (-q 7 to check) 8 => 0 Make certain all IDs in BattingPost are in Master (-q 8 to check) 9 => 0 Make certain all IDs in PitchingPost are in Master (-q 9 to check) 10 => 0 Make certain all IDs in FieldingPost are in Master (-q 10 to check) 11 => 0 Make certain all IDs in Fielding are in Master (-q 11 to check) 12 => 1 Make certain all IDs in FieldingOF are in Master (-q 12 to check) 13 => 2 Make certain all IDs in Salaries are in Master (-q 13 to check) 14 => 0 Make certain all IDs in HallOfFame are in Master (-q 14 to check) 15 => 0 Make certain all IDs in Managers are in Master (-q 15 to check) 16 => 0 Make certain all IDs in ManagersHalf are in Master (-q 16 to check) 17 => 0 Make certain all IDs in Allstar are in Master (-q 17 to check) 18 => 0 Make certain all IDs in AwardsPlayers are in Master (-q 18 to check) 19 => 4 [3 expected] Make certain all IDs in AwardsPlayers are in Batting that year (-q 19 to check) 20 => 0 Make certain all IDs in AwardsSharePlayers are in Master (-q 20 to check) 21 => 0 Make certain all IDs in AwardsSharePlayers are in Batting that year (-q 21 to check) 22 => 0 Make certain all IDs in AwardsManagers are in Master (-q 22 to check) 23 => 0 Make certain all IDs in AwardsManagers are in Managers that year (-q 23 to check) 24 => 0 [1 expected] Make certain all IDs in AwardsShareManagers are in Master (-q 24 to check) 25 => 0 [1 expected] Make certain all IDs in AwardsShareManagers are in Managers that Year (-q 25 to check) 26 => 30 Years beyond our 1871 and 2006 have no teams (-q 26 to check) 27 => 0 All the teamIDs in Batting match to Teams (-q 27 to check) 28 => 0 All the teamIDs in Pitching match to Teams (-q 28 to check) 29 => 0 All the teamIDs in BattingPost match to Teams (-q 29 to check) 30 => 0 All the teamIDs in PitchingPost match to Teams (-q 30 to check) 31 => 0 All the teamIDs in FieldingPost match to Teams (-q 31 to check) 32 => 0 All the teamIDs in Fielding match to Teams (-q 32 to check) 33 => 0 All the teamIDs in Salaries match to Teams (-q 33 to check) 34 => 0 All the teamIDs in Managers match to Teams (-q 34 to check) 35 => 0 All the teamIDs in ManagersHalf match to Teams (-q 35 to check) 36 => 0 All the team franchiseIDs match to TeamsFranchises (-q 36 to check) 37 => 0 All the teams in TeamsHalf match to actual Teams (-q 37 to check) 38 => 0 All the managers in ManagersHalf match to entries in Managers (-q 38 to check) 39 => 0 All Pitching entries have matching Fielding entries (-q 39 to check) 40 => 3 All Fielding entries have matching Pitching entries (-q 40 to check) 41 => 135 All Fielding entries have matching Batting entries (-q 41 to check) 42 => 0 All FieldingOF entries have matching Fielding entries (-q 42 to check) 43 => 3897 All Fielding entries for OFers have matching FieldingOF entries (-q 43 to check) 44 => 0 All players in BattingPost have teams in SeriesPost (-q 44 to check) 45 => 0 All players in PitchingPost have teams in SeriesPost (-q 45 to check) 46 => 0 All players in FieldingPost have teams in SeriesPost (-q 46 to check) 47 => 0 All winning teams in SeriesPost have entries in Teams (-q 47 to check) 48 => 0 All losing teams in SeriesPost have entries in Teams (-q 48 to check) 49 => 0 All teams have players in the Batting table (-q 49 to check) 50 => 0 All teams have players in the Pitching table (-q 50 to check) 51 => 0 All teams have players in the Fielding table (-q 51 to check) 52 => 0 All teams have players in the Managers table (-q 52 to check) 53 => 7 [5 expected] All players in BattingPost have entries in Batting (-q 53 to check) 54 => 1 [1 expected] All players in PitchingPost have entries in Pitching (-q 54 to check) 55 => 0 All players in FieldingPost have entries in Fielding (-q 55 to check) 56 => 0 All schoolIDs in SchoolsPlayers should have entries in Schools (-q 56 to check) 57 => 1 All playerIDs in SchoolsPlayers should have entries in master (-q 57 to check) 58 => 806 [Lots expected] All players in Salaries should have entries in Batting unless injured (-q 58 to check) 59 => 2 All players in Salaries should have entries in Master (-q 59 to check) ---------------------------------------------------------------------- QUERIES USED ---------------------------------------------------------------------- # 60 queries # Query 1 => Unique playerIDs SELECT playerID as ID, count(*) as foo from Master WHERE playerID != '' GROUP BY ID HAVING foo > 1 # Query 2 => Unique hofIDs SELECT hofID as ID, count(*) as foo from Master WHERE hofID != '' GROUP BY ID HAVING foo > 1 # Query 3 => Unique managerIDs SELECT managerID as ID, count(*) as foo from Master WHERE managerID !='' GROUP BY ID HAVING foo > 1 # Query 4 => Make certain the playerIDs are tightly packed select replace(playerID,right(playerID,2),"") as lefty, count(*) as IDs, max(right(playerID,2)) as highCount from Master where right(playerID,2) < 90 AND playerID !='' group by lefty having IDs!=highCount # Query 5 => [1 expected] Make certain all playersIDs in Master are in Batting SELECT Master.playerID, lahmanID, managerID FROM Master LEFT JOIN Batting using (playerID) WHERE Batting.playerID is NULL AND Master.playerID !='' # Query 6 => Make certain all IDs in Batting are in Master SELECT Batting.playerID, yearID, teamID, lgID FROM Batting LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 7 => Make certain all IDs in Pitching are in Master SELECT Pitching.playerID, yearID, teamID, lgID FROM Pitching LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 8 => Make certain all IDs in BattingPost are in Master SELECT BattingPost.playerID, yearID, teamID, lgID, round FROM BattingPost LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 9 => Make certain all IDs in PitchingPost are in Master SELECT PitchingPost.playerID, yearID, teamID, lgID,round FROM PitchingPost LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 10 => Make certain all IDs in FieldingPost are in Master SELECT FieldingPost.playerID, yearID, teamID, lgID,round FROM FieldingPost LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 11 => Make certain all IDs in Fielding are in Master SELECT Fielding.playerID, yearID, teamID, lgID FROM Fielding LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 12 => Make certain all IDs in FieldingOF are in Master SELECT FieldingOF.playerID, yearID, stint FROM FieldingOF LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 13 => Make certain all IDs in Salaries are in Master SELECT Salaries.playerID, yearID, teamID, lgID FROM Salaries LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 14 => Make certain all IDs in HallOfFame are in Master SELECT HallOfFame.hofID, inducted FROM HallOfFame LEFT JOIN Master using (hofID) WHERE Master.hofID is NULL # Query 15 => Make certain all IDs in Managers are in Master SELECT Managers.managerID, yearID, teamID, lgID FROM Managers LEFT JOIN Master using (managerID) WHERE Master.managerID is NULL # Query 16 => Make certain all IDs in ManagersHalf are in Master SELECT ManagersHalf.managerID, yearID, teamID, lgID, half FROM ManagersHalf LEFT JOIN Master using (managerID) WHERE Master.managerID is NULL # Query 17 => Make certain all IDs in Allstar are in Master SELECT Allstar.playerID, yearID FROM Allstar LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 18 => Make certain all IDs in AwardsPlayers are in Master SELECT AwardsPlayers.playerID, yearID FROM AwardsPlayers LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 19 => [3 expected] Make certain all IDs in AwardsPlayers are in Batting that year SELECT AwardsPlayers.playerID, AwardsPlayers.yearID FROM AwardsPlayers LEFT JOIN Batting using (playerID, yearID) WHERE Batting.playerID is NULL # Query 20 => Make certain all IDs in AwardsSharePlayers are in Master SELECT AwardsSharePlayers.playerID, yearID FROM AwardsSharePlayers LEFT JOIN Master using (playerID) WHERE Master.playerID is NULL # Query 21 => Make certain all IDs in AwardsSharePlayers are in Batting that year SELECT AwardsSharePlayers.playerID, AwardsSharePlayers.yearID FROM AwardsSharePlayers LEFT JOIN Batting using (playerID, yearID) WHERE Batting.playerID is NULL # Query 22 => Make certain all IDs in AwardsManagers are in Master SELECT AwardsManagers.managerID, yearID FROM AwardsManagers LEFT JOIN Master using (managerID) WHERE Master.managerID is NULL # Query 23 => Make certain all IDs in AwardsManagers are in Managers that year SELECT AwardsManagers.managerID, AwardsManagers.yearID FROM AwardsManagers LEFT JOIN Managers using (managerID, yearID) WHERE Managers.managerID is NULL # Query 24 => [1 expected] Make certain all IDs in AwardsShareManagers are in Master SELECT AwardsShareManagers.managerID, yearID FROM AwardsShareManagers LEFT JOIN Master using (managerID) WHERE Master.managerID is NULL # Query 25 => [1 expected] Make certain all IDs in AwardsShareManagers are in Managers that Year SELECT AwardsShareManagers.managerID, AwardsShareManagers.yearID FROM AwardsShareManagers LEFT JOIN Managers using (managerID,yearID) WHERE Managers.managerID is NULL # Query 26 => Years beyond our 1871 and 2006 have no teams SELECT yearID, teamID, lgID FROM Teams WHERE yearID < 1871 OR yearID > 2006 # Query 27 => All the teamIDs in Batting match to Teams SELECT Batting.yearID, Batting.teamID, Batting.lgID, playerID FROM Batting left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 28 => All the teamIDs in Pitching match to Teams SELECT Pitching.yearID, Pitching.teamID, Pitching.lgID, playerID FROM Pitching left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 29 => All the teamIDs in BattingPost match to Teams SELECT BattingPost.yearID, BattingPost.teamID, BattingPost.lgID, playerID FROM BattingPost left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 30 => All the teamIDs in PitchingPost match to Teams SELECT PitchingPost.yearID, PitchingPost.teamID, PitchingPost.lgID, playerID FROM PitchingPost left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 31 => All the teamIDs in FieldingPost match to Teams SELECT FieldingPost.yearID, FieldingPost.teamID, FieldingPost.lgID, playerID FROM FieldingPost left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 32 => All the teamIDs in Fielding match to Teams SELECT Fielding.yearID, Fielding.teamID, Fielding.lgID, playerID FROM Fielding left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 33 => All the teamIDs in Salaries match to Teams SELECT Salaries.yearID, Salaries.teamID, Salaries.lgID, playerID FROM Salaries left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 34 => All the teamIDs in Managers match to Teams SELECT Managers.yearID, Managers.teamID, Managers.lgID, managerID FROM Managers left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 35 => All the teamIDs in ManagersHalf match to Teams SELECT ManagersHalf.yearID, ManagersHalf.teamID, ManagersHalf.lgID, managerID FROM ManagersHalf left join Teams using (yearID, teamID, lgID) WHERE Teams.teamID is NULL # Query 36 => All the team franchiseIDs match to TeamsFranchises SELECT Teams.yearID, Teams.teamID, Teams.lgID, Teams.franchID FROM Teams LEFT JOIN TeamsFranchises using (franchID) WHERE TeamsFranchises.franchID is NULL # Query 37 => All the teams in TeamsHalf match to actual Teams SELECT TeamsHalf.yearID, TeamsHalf.teamID, TeamsHalf.lgID, TeamsHalf.half FROM TeamsHalf LEFT JOIN Teams using (yearID, teamID, lgID) WHERE Teams.teamID IS NULL # Query 38 => All the managers in ManagersHalf match to entries in Managers SELECT ManagersHalf.yearID, ManagersHalf.teamID, ManagersHalf.lgID, ManagersHalf.managerID FROM ManagersHalf LEFT JOIN Managers using (yearID, teamID, lgID, managerID) WHERE Managers.managerID IS NULL # Query 39 => All Pitching entries have matching Fielding entries SELECT Pitching.yearID, Pitching.teamID, Pitching.lgID, Pitching.playerID FROM Pitching LEFT JOIN Fielding using (yearID, teamID, lgID, playerID, stint) WHERE Fielding.POS='P' AND Fielding.playerID IS NULL # Query 40 => All Fielding entries have matching Pitching entries SELECT Fielding.yearID, Fielding.teamID, Fielding.lgID, Fielding.playerID FROM Fielding LEFT JOIN Pitching using (yearID, teamID, lgID, playerID, stint) WHERE Fielding.POS='P' AND Pitching.playerID IS NULL # Query 41 => All Fielding entries have matching Batting entries SELECT Fielding.yearID, Fielding.teamID, Fielding.lgID, Fielding.playerID FROM Fielding LEFT JOIN Batting using (yearID, teamID, lgID, playerID, stint) WHERE Batting.playerID IS NULL # Query 42 => All FieldingOF entries have matching Fielding entries SELECT FieldingOF.yearID, FieldingOF.stint, FieldingOF.playerID FROM FieldingOF LEFT JOIN Fielding using (yearID, playerID, stint) WHERE Fielding.POS='OF' AND Fielding.playerID IS NULL # Query 43 => All Fielding entries for OFers have matching FieldingOF entries SELECT Fielding.yearID, Fielding.stint, Fielding.playerID FROM Fielding LEFT JOIN FieldingOF using (yearID, playerID, stint) WHERE Fielding.POS='OF' AND FieldingOF.playerID IS NULL # Query 44 => All players in BattingPost have teams in SeriesPost SELECT BattingPost.yearID, BattingPost.teamID, BattingPost.lgID, BattingPost.round, BattingPost.playerID FROM BattingPost LEFT JOIN SeriesPost ON SeriesPost.yearID=BattingPost.yearID AND SeriesPost.round=BattingPost.round AND (teamIDwinner=teamID OR teamIDloser=teamID) AND (lgIDwinner=lgID OR lgIDloser=lgID) WHERE SeriesPost.round IS NULL # Query 45 => All players in PitchingPost have teams in SeriesPost SELECT PitchingPost.yearID, PitchingPost.teamID, PitchingPost.lgID, PitchingPost.round, PitchingPost.playerID FROM PitchingPost LEFT JOIN SeriesPost ON SeriesPost.yearID=PitchingPost.yearID AND SeriesPost.round=PitchingPost.round AND (teamIDwinner=teamID OR teamIDloser=teamID) AND (lgIDwinner=lgID OR lgIDloser=lgID) WHERE SeriesPost.round IS NULL # Query 46 => All players in FieldingPost have teams in SeriesPost SELECT FieldingPost.yearID, FieldingPost.teamID, FieldingPost.lgID, FieldingPost.round, FieldingPost.playerID FROM FieldingPost LEFT JOIN SeriesPost ON SeriesPost.yearID=FieldingPost.yearID AND SeriesPost.round=FieldingPost.round AND (teamIDwinner=teamID OR teamIDloser=teamID) AND (lgIDwinner=lgID OR lgIDloser=lgID) WHERE SeriesPost.round IS NULL # Query 47 => All winning teams in SeriesPost have entries in Teams SELECT SeriesPost.yearID, SeriesPost.teamIDwinner, SeriesPost.lgIDwinner, round FROM SeriesPost LEFT JOIN Teams ON SeriesPost.yearID=Teams.yearID AND teamIDwinner=teamID and lgIDwinner=lgID WHERE Teams.teamID IS NULL # Query 48 => All losing teams in SeriesPost have entries in Teams SELECT SeriesPost.yearID, SeriesPost.teamIDloser, SeriesPost.lgIDloser, round FROM SeriesPost LEFT JOIN Teams ON SeriesPost.yearID=Teams.yearID AND teamIDloser=teamID and lgIDloser=lgID WHERE Teams.teamID IS NULL # Query 49 => All teams have players in the Batting table SELECT Teams.yearID, Teams.teamID, Teams.lgID, count(*) as Players FROM Teams LEFT JOIN Batting using(teamID,yearID,lgID) GROUP BY Teams.teamID, Teams.lgID, Teams.yearID HAVING Players = 0 # Query 50 => All teams have players in the Pitching table SELECT Teams.yearID, Teams.teamID, Teams.lgID, count(*) as Players FROM Teams LEFT JOIN Pitching using(teamID,yearID,lgID) GROUP BY Teams.teamID, Teams.lgID, Teams.yearID HAVING Players = 0 # Query 51 => All teams have players in the Fielding table SELECT Teams.yearID, Teams.teamID, Teams.lgID, count(*) as Players FROM Teams LEFT JOIN Fielding using(teamID,yearID,lgID) GROUP BY Teams.teamID, Teams.lgID, Teams.yearID HAVING Players = 0 # Query 52 => All teams have players in the Managers table SELECT Teams.yearID, Teams.teamID, Teams.lgID, count(*) as Players FROM Teams LEFT JOIN Managers using(teamID,yearID,lgID) GROUP BY Teams.teamID, Teams.lgID, Teams.yearID HAVING Players = 0 # Query 53 => [5 expected] All players in BattingPost have entries in Batting SELECT BattingPost.yearID, round, BattingPost.teamID, BattingPost.playerID FROM BattingPost LEFT JOIN Batting using(teamID,yearID,lgID,playerID) WHERE Batting.playerID IS NULL # Query 54 => [1 expected] All players in PitchingPost have entries in Pitching SELECT PitchingPost.yearID, round, PitchingPost.teamID, PitchingPost.playerID FROM PitchingPost LEFT JOIN Pitching using(teamID,yearID,lgID,playerID) WHERE Pitching.playerID IS NULL # Query 55 => All players in FieldingPost have entries in Fielding SELECT FieldingPost.yearID, round, FieldingPost.teamID, FieldingPost.playerID FROM FieldingPost LEFT JOIN Fielding using(teamID,yearID,lgID,playerID) WHERE Fielding.playerID IS NULL # Query 56 => All schoolIDs in SchoolsPlayers should have entries in Schools SELECT SchoolsPlayers.playerID, SchoolsPlayers.schoolID, yearMin FROM SchoolsPlayers LEFT JOIN Schools using(schoolID) WHERE Schools.schoolID IS NULL ORDER BY SchoolsPlayers.playerID ASC # Query 57 => All playerIDs in SchoolsPlayers should have entries in master SELECT SchoolsPlayers.playerID, SchoolsPlayers.playerID, yearMin FROM SchoolsPlayers LEFT JOIN Master using(playerID) WHERE Master.playerID IS NULL ORDER BY SchoolsPlayers.playerID ASC # Query 58 => [Lots expected] All players in Salaries should have entries in Batting unless injured SELECT Salaries.yearID, Salaries.teamID, Salaries.playerID FROM Salaries LEFT JOIN Batting using(teamID,yearID,lgID,playerID) WHERE Batting.playerID IS NULL ORDER BY Salaries.yearID ASC # Query 59 => All players in Salaries should have entries in Master SELECT Salaries.yearID, Salaries.teamID, Salaries.playerID FROM Salaries LEFT JOIN Master using(playerID) WHERE Master.playerID IS NULL # Query 60 => quer ----------------------------------------------------------------------