Advanced SQL Queries
Declaring/invoking functions/procedures, executing triggers, and performing other advanced SQL queries—in MariaDB.
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 these advanced queries on X database on MariaDB.
Note: MariaDB was developed by Ulf Michael Widenius, the co-founder of MySQL, as a fork of MySQL in 2009 due to concerns over Oracle’s acquisition. It offers improved features, performance, and compatibility with MySQL.
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
- Bartholomew, D. (2015). Getting Started with MariaDB (2nd ed.). Packt.
- Hoffer, J. A., Prescott, M. B., & McFadden, F. R. (2007). Modern Database Management (8th ed.). Pearson.