Managing databases has always been one of the most fun things for me, optimising queries, finding inefficiencies, figuring out how to squeeze more performance out of the DB servers.
But most of it has come from just trying whatever I can think of, and I'm sure I'm missing some fundamentals or more advanced techniques, so I figured I'd ask y'all for guidance.
- Developing Time-Oriented Database Applications in SQL by Rich. Snodgrass: https://www.amazon.com/Developing-Time-Oriented-Database-App...
-----
Anything by Joe Celko:
- SQL for Smarties: https://www.amazon.com/gp/product/B00R17NZZC
- SQL Programming Style: https://www.amazon.com/gp/product/B006L21AO6
- Trees and Hierarchies in SQL for Smarties: https://www.amazon.com/gp/product/B006Y8MKUU
- Thinking in Sets: https://www.amazon.com/gp/product/B001AODZZC
-------
- SQL Antipatterns by Bill Karwin: https://www.amazon.com/SQL-Antipatterns-Programming-Pragmati...
I learned a big part from 'An introduction to Database Systems' from C.J.Date. The core understanding of relational databases is in there. It was at the time a book that needed cleaning up and reorganizing, but a few editions have passed by now, so maybe that has happened.
I also learned a lot of Tom Kyte's Expert 1 on 1. Beware: A huge tome, not only good for swatting flies, it can manage small rodents as well. I had the first or second edition which is probably pretty dated by now, but it is a good book to pick up the DBA attitude and learn advanced querying. If you use it as inspiration and not as a reference, it will probably still work.
Even if the 2nd book is about Oracle, I would suggest to start learning postgres, it is a cleaner, better structured database today.
Ralph Kimball and Margy Ross, The Data Warehouse Toolkit -- even if you're in an OLTP context, it's useful to know about warehousing techniques
Dmitri Fontaine, The Art of PostgreSQL -- cookbook approach to modern SQL with plenty of explanations
Not a book but Heinz Klein and Kalle Lyytinen's paper "Towards a New Understanding of Data Modeling" in the Software Development and Reality Construction remains a must-read 30 years later. The organizers of that conference have published a free PDF copy of SDRC if you look around a bit.