6 ms·
Advanced SQL and database books and resources
- etiennebch 7y agoCmu course is a must watch really
- cm2187 7y agoParticularly given that the professor is doing his lecture from his bathtub!
- zerr 7y agoYeah, it's good but somewhat diverged from the practical part unless you are an onsite student. I wonder what are alternatives (video lectures or textual resources) with more hands-on approach implementing database systems.
- etiennebch 7y agoNot sure if this is what you are looking for but I found this paper useful on architecting databases. I read it as a complement to the CMU videos. https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf Otherwise there is surprisingly a lack of information about architecting databases online and you have to go to the source code of SQLite and PostgreSQL. I suspect this is because databases are some of the most complex systems in computer science and involve many trade secrets.
- SQLite 7y agoOn-line docs describing the design and internal operation of SQLite include: https://sqlite.org/arch.html https://sqlite.org/arch.html https://sqlite.org/optoverview.html https://sqlite.org/optoverview.html https://sqlite.org/opcode.html https://sqlite.org/opcode.html https://sqlite.org/queryplanner.html https://sqlite.org/queryplanner.html https://sqlite.org/atomiccommit.html https://sqlite.org/atomiccommit.html Inspired by this post, I started yet another: https://sqlite.org/draft/howitworks.html https://sqlite.org/draft/howitworks.html
- polskibus 7y agoIt's awesome, but not for learning how to solve problems with SQL.
- pocket_cheese 7y agoOMG yes! Andy Pavlo is such a good teacher. I watched his entire lecture series, and it really helped me prepare for an interview. I couldn't recommend his MOOC enough if you really want to know how a database works. I just wish that the homework assignments were available to the public.
- diehunde 7y agoIt is, but not if you're trying to get better at SQL.
- roenxi 7y agoThere is something to be said for having an advanced knowledge of SQL and then carefully staying a very long distance from complicated queries. I can't think of a language where the syntax buckles worse than SQL when things get complicated. Sometimes it makes sense to roll up the sleeves and do unspeakable things with SQL, but that isn't some sort of win.
- C1sc0cat 7y agoI recall seeing some gnarly SQL at British Telecom over 2k lines. Id agree with you about keeping it as simple as possible
- hobs 7y agoPeople do dumb things with SQL, but depending on the query/set of columns, 2k is a pretty quick line count to reach. Just dont use select *, have a wide table, do more than a few trivial operations moving data around, and you'll get to 2k pretty quick. That being said, people do dumb stuff with SQL all the time; The worst query I have ever seen was 11MB, megabytes. The worst query plan I have ever seen generated a plan that was ~130MB. I regularly have people ask questions in #sqlhelp (https://sqlps.io/slack/ https://sqlps.io/slack/ SQL Server focused) like "Why can I only have 2100 parameters to a stored procedure?" (dude was super mad that I asked if his design might use some rework.)
- kthejoker2 7y agoI've told SQL learners before There is no Advanced SQL; there's only "a very large amount of SQL", "exotic SQL", and "SQL no one should have written."
- Pamar 7y agoAre you sure it wasn't either: + a stored procedure or + the end-product of an ORM tool?
- sails 7y agoI've been having a bit of joy using Dataform [] recently to "modularise" SQL code into more manageable views. It brings some of the best practices of software development to the SQL hellscape, making management of complicated SQL a pleasure. [] https://dataform.co/product https://dataform.co/product (this is a plug from a happy customer!)
- branko_d 7y agoIf you are an application programmer needing to query the database, I wholeheartedly recommend "SQL Performance Explained" by Markus Winand, or his website: https://use-the-index-luke.com/ https://use-the-index-luke.com/
- _gni9 7y agothat was eye-opening indeed. Before reading his blog, I had no clue how indexes worked and how to write SQL that match the index structure (for instance the order of conditions in the where clause matter if you want to leverage a multi-column index).
- MarkusWinand 7y ago> for instance the order of conditions in the where clause matter if you want to leverage a multi-column index Ups, if this is you take away, then I've done something wrong. Let me correct that: The order of columns in an index matters, not the order of conditions in the where clause.
- etiennebch 7y agoAh thanks for the clarification !
- therealdrag0 7y agoAnd IIRC this holds MongoDB and I'd assume other non-SQL DBs. If you have an index on <companyId, userId>, and you query with a userID only, that index wont be used. But if the index was <userId, companyId> then that index would be used. Or if you supplied both userId and companyId in your query, then either index would work.
- mrits 7y agoI think you just need to realize that an index on <companyId, userId> is a single index
- 7y ago
- polskibus 7y agoI don't see Celko books https://en.wikipedia.org/wiki/Joe_Celko https://en.wikipedia.org/wiki/Joe_Celko they're a must have resource for advanced SQL.
- eatonphil 7y agoDo you suggest any one in particular? I've been daunted by their size.
- nickpeterson 7y agoSQL for Smarties tends to have a nice coverage of common problems encountered and how to resolve them.
- LunaSea 7y agoI couldn't dislike the marketing of "The Art of PostgreSQL" more ... A website that looks like a SaaS. Hugely overpriced paperback version. No book summary available. A free chapter banner that doesn't link anywhere. No possibility to buy on different stores.
- mosselman 7y agoI bought it through work and it is very missable to be honest. Very shallow.
- taffit 7y agoFully agree with you. I bought it, just to discover, that it is somehow just a simple introduction to SQL. Also what I found irritating: Nowhere on the marketing page of the book I found a reference to the PostgreSQL-version for which it was written or applicable. Most probably because it is only very loosely coupled with PostgreSQL.
- tarasmatsyk 7y agoI found the book apprisal somewhere on Reddit in "advanced books on postgres". I expected to find postgres optimization and at least a few pages on how to scale it (or where to look for solutions), instead I was dissapointed. While it's still a very decent book on SQL syntaxes and advanced queries (I bet even 10y+ PostgreSQL users don't know half of what PostgreSQL can do) here is why I don't recommend it: - poorly written instructions on how to set up Postgres 11 with all those crazy extensions - No epub version (it's simply broken and nobody responds on my emails, instead I get a reminder to continue my learning course which has 2 links, a short intro and a link to the book. I've stopped at 50% just because the book was downloaded from another browser and now I get these irritating emails) - There is a lot of Python in the book, I want to learn SQL and PostgreSQL, not Python or any other sort of languages. I use Py professionally but it does not mean I want it to be tied to SQL. - There are unrelated interviews, for instance, a guy who worked on Django ORMs (how is this related to my SQL knowledge) Don't get me wrong, don't treat this comment as a rant about how bad the book is, the SQL tricks are really awesome there, however if you think about buying this book, go for 49$ version and expect to have some pain to setup everything. I am like 50/50 on asking for refund
- zerkten 7y agoRob Conery's A Curious Moon (https://bigmachine.io/products/a-curious-moon/ https://bigmachine.io/products/a-curious-moon/) is an interesting read for a PostgreSQL book. I wouldn't say it tries to be an advanced book by design, but I learned a lot of modern SQL from it. I think the number of people needing truly advanced books is small whereas a lot of people need to jump from beginners to intermediate, or intermediate to advanced.
- rietta 7y agoAdvanced PostgreSQL and NASA data ... sold. Shut up and take my $30. Instabuy :-)
- rsecora 7y agoThe PostgreSQL exercises web [1] is an awesome intro to basic and medium level topics. It has been discussed previously in HN [2]. [1] https://pgexercises.com/ https://pgexercises.com/ [2] https://news.ycombinator.com/item?id=12022953 https://news.ycombinator.com/item?id=12022953
- Pamar 7y agoA billion "Up" for The Art of SQL by Faroult
- ComodoHacker 7y agoIf you want something more advanced and fundamental, I'd recommend classic Database System Implementation[0] by Hector Garcia-Molina; Jennifer D. Widom; Jeffrey D. Ullman. It's still relevant. Technologies change, but fundamental principles and algorithms... change order of magnitude slower. And yes, it's that Ullman. 0. https://www.amazon.com/dp/0130402648 https://www.amazon.com/dp/0130402648
- beckingz 7y agoReally enjoy SQL Antipatterns by Bill Karwin.
- fronofro 7y agoI am a bit biased here in terms of resources, I worked with a few people to create a free resource that covers intro to sql optimization: https://dataschool.com/sql-optimization https://dataschool.com/sql-optimization I also created a bunch of animations to help people build mental models for joins, aggregations, window functions, and subqueries which I find is the main blocker for people using SQL optimally: https://dataschool.com/how-to-teach-people-sql/ https://dataschool.com/how-to-teach-people-sql/
- tuatoru 7y agoI have long thought that the best training for database is a course on set theory and group theory, coupled with a close reading of William Kent's "Data and Reality". The book is a mess, but its structure illustrates its several points.