Learning SQL resources

September 26, 2018
learning SQL

The funny thing is - I don’t remember how and when I learned SQL. I was dabbling with some horrid VB code in the early 2000s, connecting excel sheets, access database files and using witchcraft acronyms like ADO and DAO. Then I got into some web development, through django and, later, flask and there was again this protective layer of ORMs, between me, a novice programmer, and the terrible world of tables, queries and joins. I remember using SqlObject and PeeWee - two younger python siblings of the mighty SQLAlchemy. Now, I know that things really took a turn with Ruby’s ActiveRecord, back when Rails were all the rage and scriptaculous was more popular than jquery, so it was really long time ago.

Anyway, the first databases that I designed were made through code - a VB GUI or a python script. Then, several years ago, while I was building my next-big-thing web app (a born dead sports management system), I had to get my hands dirty! I just felt the need to solidify my knowledge so I gradually delved into pure sql. The end result was a fairly complex schema, connecting a users table, friendship joins, geospatial locations data, instant messages table and several more. Below are listed some of the resources that I have used over the years and that I found valuable and useful.

Web resources

This comparison of python ORMS is like really, really old, but I guess it is still worth reading, as it gives you a taste of each of the main options.

Charles Leifer, the author of PeeWee and of this great book has tons of high quality posts about programming, python and sqlite (my favorite database). You can start here and then work your way up.

The ZetCode tutorial on python and sqlite is also very old, but is a good gentle introduction to sql through (again) python.

Mode Analytics is one of those companies which, in order to market their services, creates a bunch of high quality tutorials and guides in order to get you up and running (but it’s also great SEO!). DigitalOcean is an example of this strategy - they have literally hundreds of tutorials on setting up hardware, servers, operating systems and whatnot. Anyway Mode’s tutorial on SQL is aimed at beginners and it is pretty cool. I must admit I haven’t tried Mode for some analytics work, but I guess I’ll give it a try, although competing with Anaconda is really tough.

For those more proficient with Pandas, this Greg Reda’s Pydata video is a real gem.

Brad Traversy has one of the best and most popular channels on youtube, where he teaches the art of Node.js, web design, javascript and other interesting technologies. His video on SQL is one of the best in the business and is aimed at web developers. Brad is a good teacher. And thorough. This result will probably pop-up in the sidebar. It is a good video as well, oriented towards Microsoft SQL server users, but the underlying concepts remain the same. Highly recommended!

Sebastian Rashcka is one of the most prominent authors when it comes to python data science. His book Python Machine Learning has quickly become a classic in the DataSci community. Sebastian’s blog, however, is full of interesting snippets and his sqlite + python post can become the foundation of a small project or provide inspiration.

I found SQLBolt very valuable - I used it recently in order to refresh my knowledge and I recommend it wholeheartedly to any sql newcomer.

Books

Honestly, I haven’t found much time for reading books on SQL. I will recommend only two books, that I actually read from start to finish and in which I found the majority of answers that I was looking for.

The first one is Getting Started with SQL - a very gentle introduction using SqLite, without any programming language. The provided example databases are simple enough in order to enable you to play with them and get a feel of the language, yet complex just enough to illustrate the main points.

Learning SQL is a longer book which delves much deeper into various aspects of SQL, using the ubiquitous MySQL database server. Bealieu explains the intricacies of joins, aggregates, subjoins and sets using numerous examples which are very applicable to real-life scenarios.

Courses

I enrolled in Udemy’s MySQL for Data Analytics and Business Intelligence and I haven’t finished it yet. Still, the lessons that I went through were very interesting and thorough. Once I find the time to view at least 70% of the material, I’ll post an update. For now, it looks very good - like a serious one semester course that I wish existed 10 or 15 years ago.