More Complex SQL Queries
Performing join expressions, view definitions, transactions, integrity constraints, and other intermediate SQL queries—in PostgreSQL.
Intermediate SQL queries, such as functions, procedures, and triggers, are used for modularity (functions and procedures enhance code organization and reusability, reducing redundancy in complex queries); data Integrity (triggers enforce data consistency and integrity, executing actions automatically when specific events occur, maintaining data quality); and business logic (functions and procedures encapsulate business logic, enabling complex calculations and data transformations, streamlining application development).
Let’s perform these intermediate queries on X database on PostgreSQL.
Note: MySQL was created by Ulf Michael Widenius in 1995 as an open-source relational database management system (RDBMS). It gained popularity for web applications and became widely used in the early 2000s. Despite competition, it remains relevant due to its simplicity, performance, scalability, and active community support for modern web development and enterprise solutions
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
- Obe, R. O., & Hsu, L. S. (2017). PostgreSQL: Up and Running (3rd ed.). O’Reilly.
- Hoffer, J. A., Prescott, M. B., & McFadden, F. R. (2007). Modern Database Management (8th ed.). Pearson.