Common SQL Queries for Retrieving and Aggregating Relational Data
Performing queries on the Lahman baseball database—in SQLite.
Structured Query Language (SQL, aka “sequel”) is a standard language for managing relational databases. It remains relevant due to its simplicity, flexibility, and widespread adoption. SQL allows users to efficiently interact with databases, retrieve, update, and manage data, making it a crucial tool for businesses and developers in handling structured information effectively.
Let’s perform common queries for retrieving and aggregating data on the Lahman baseball database on SQLite.
Note: SQLite is a lightweight, embedded, and serverless open-source relational database management system (RDBMS). Unlike most other SQL databases, SQLite does not have a separate server process, and it is self-contained within the application. It is designed to be simple to set up and use, making it a popular choice for mobile and embedded applications.
Getting Started
SELECT * FROM teams
yearID | lgID | teamID | franchID | divID | Rank | G | Ghome | W | L | DivWin | WCWin | LgWin | WSWin | R | AB | H | X2B | X3B | HR | BB | SO | SB | CS | HBP | SF | RA | ER | ERA | CG | SHO | SV | IPouts | HA | HRA | BBA | SOA | E | DP | FP | name | park | attendance | BPF | PPF | teamIDBR | teamIDlahman45 | teamIDretro |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1871 | NA | BS1 | BNA | NA | 3 | 31 | NA | 20 | 10 | NA | NA | N | NA | 401 | 1372 | 426 | 70 | 37 | 3 | 60 | 19 | 73 | 16 | NA | NA | 303 | 109 | 3.5 | 22 | 1 | 3 | 828 | 367 | 2 | 42 | 23 | 243 | 24 | 0.83 | Boston Red Stockings | South End Grounds I | NA | 103 | 98 | BOS | BS1 | BS1 |
1871 | NA | CH1 | CNA | NA | 2 | 28 | NA | 19 | 9 | NA | NA | N | NA | 302 | 1196 | 323 | 52 | 21 | 10 | 60 | 22 | 69 | 21 | NA | NA | 241 | 77 | 2.8 | 25 | 0 | 1 | 753 | 308 | 6 | 28 | 22 | 229 | 16 | 0.83 | Chicago White Stockings | Union Base-Ball Grounds | NA | 104 | 102 | CHI | CH1 | CH1 |
1871 | NA | CL1 | CFC | NA | 8 | 29 | NA | 10 | 19 | NA | NA | N | NA | 249 | 1186 | 328 | 35 | 40 | 7 | 26 | 25 | 18 | 8 | NA | NA | 341 | 116 | 4.1 | 23 | 0 | 0 | 762 | 346 | 13 | 53 | 34 | 234 | 15 | 0.82 | Cleveland Forest Citys | National Association Grounds | NA | 96 | 100 | CLE | CL1 | CL1 |
1871 | NA | FW1 | KEK | NA | 7 | 19 | NA | 7 | 12 | NA | NA | N | NA | 137 | 746 | 178 | 19 | 8 | 2 | 33 | 9 | 16 | 4 | NA | NA | 243 | 97 | 5.2 | 19 | 1 | 0 | 507 | 261 | 5 | 21 | 17 | 163 | 8 | 0.80 | Fort Wayne Kekiongas | Hamilton Field | NA | 101 | 107 | KEK | FW1 | FW1 |
1871 | NA | NY2 | NNA | NA | 5 | 33 | NA | 16 | 17 | NA | NA | N | NA | 302 | 1404 | 403 | 43 | 21 | 1 | 33 | 15 | 46 | 15 | NA | NA | 313 | 121 | 3.7 | 32 | 1 | 0 | 879 | 373 | 7 | 42 | 22 | 235 | 14 | 0.84 | New York Mutuals | Union Grounds (Brooklyn) | NA | 90 | 88 | NYU | NY2 | NY2 |
1871 | NA | PH1 | PNA | NA | 1 | 28 | NA | 21 | 7 | NA | NA | Y | NA | 376 | 1281 | 410 | 66 | 27 | 9 | 46 | 23 | 56 | 12 | NA | NA | 266 | 137 | 5.0 | 27 | 0 | 0 | 747 | 329 | 3 | 53 | 16 | 194 | 13 | 0.84 | Philadelphia Athletics | Jefferson Street Grounds | NA | 102 | 98 | ATH | PH1 | PH1 |
1871 | NA | RC1 | ROK | NA | 9 | 25 | NA | 4 | 21 | NA | NA | N | NA | 231 | 1036 | 274 | 44 | 25 | 3 | 38 | 30 | 53 | 10 | NA | NA | 287 | 108 | 4.3 | 23 | 1 | 0 | 678 | 315 | 3 | 34 | 16 | 220 | 14 | 0.82 | Rockford Forest Citys | Agricultural Society Fair Grounds | NA | 97 | 99 | ROK | RC1 | RC1 |
1871 | NA | TRO | TRO | NA | 6 | 29 | NA | 13 | 15 | NA | NA | N | NA | 351 | 1248 | 384 | 51 | 34 | 6 | 49 | 19 | 62 | 24 | NA | NA | 362 | 153 | 5.5 | 28 | 0 | 0 | 750 | 431 | 4 | 75 | 12 | 198 | 22 | 0.84 | Troy Haymakers | Haymakers’ Grounds | NA | 101 | 100 | TRO | TRO | TRO |
1871 | NA | WS3 | OLY | NA | 4 | 32 | NA | 15 | 15 | NA | NA | N | NA | 310 | 1353 | 375 | 54 | 26 | 6 | 48 | 13 | 48 | 13 | NA | NA | 303 | 137 | 4.4 | 32 | 0 | 0 | 846 | 371 | 4 | 45 | 13 | 218 | 20 | 0.85 | Washington Olympics | Olympics Grounds | NA | 94 | 98 | OLY | WS3 | WS3 |
1872 | NA | BL1 | BLC | NA | 2 | 58 | NA | 35 | 19 | NA | NA | N | NA | 617 | 2571 | 753 | 106 | 31 | 14 | 29 | 28 | 53 | 18 | NA | NA | 434 | 166 | 2.9 | 48 | 1 | 1 | 1548 | 573 | 3 | 63 | 77 | 432 | 22 | 0.83 | Baltimore Canaries | Newington Park | NA | 106 | 102 | BAL | BL1 | BL1 |
References
- DeBarros, A. (2022). Practical SQL (2nd ed.). No Starch Press.
- Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts (3rd ed.). McGraw Hill.
- Beaulieu, A. (2020). Learning SQL (7th ed.). McGraw Hill.
- Lemahieu, W., vanden Broucke, S., & Baesens, B. (2018). Principles of Database Management: The Practical Guide to Storing, Managing and Analyzing Big and Small Data. Cambridge University Press. https://doi.org/10.1017/9781316888773
- Hoffer, J. A., Prescott, M. B., & McFadden, F. R. (2007). Modern Database Management (8th ed.). Pearson.