Setting up for 'SQL Queries for Mere Mortals'
Article Table of Contents
This tweet is from… a while ago. Turns out I didn’t dig into this book, because the pace at Turing didn’t allow for a few weeks of thinking just about SQL.
yes, I'm digging into sql to better my AR skills, and ultimately whatever I need to use next. pic.twitter.com/UhjyGKv1FQ
— Josh Thompson (@josh_works) April 24, 2017
I’ve got more time in my life now, and the company I work for makes heavy use of SQL queries, so I’m spending a little time each day working through this book. The following started as a gist, and I’m putting here, so I can more easily share it with others.
Why Study SQL #
Pretty much everything on the internet lives in a database. I figure any boost to my SQL skills will provide outsized returns down the road.
For example, just today I used some basic SQL queries to validate an assumption I had about the frequencies of null values in certain places in our database. I wouldn’t have even thought to try that, if I didn’t know in advance what I could do with a basic SQL query.
If you buy this book, and want to get set up with it’s accompanying data on your own computer, so you can practice yourself and follow along with the book… read on.
The friction of getting set up kept me from getting started with this book, and I don’t want that to happen to you too. :)
I’m working on wrapping my head around SQL. Be it MySql, SQLite, PostgreSQL, etc - these are all relational database management tools, and (I assume) they have much in common with each other.
I’ve got a copy of SQL Queries for Mere Mortals, and I plan on spending some time each day over the next ~month working through the lessons.
Lesson 1 is “setting the damn thing up”. I struggled so much getting it set up, I wrote this gist and gave it to a former coworker/awesome developer when he offered to help.
Turns out, he helped me set it up! I got it working!
And then Turing took over my life, and I stopped studying, then I got a job, and moved machines, and had to start the install from scratch.
So, I’m writing this guide as if you, dear reader, wanted to follow along through the book, and needed help getting the “dev environment” set up locally.
Step 1 - install MySQL on your machine #
note: I ended up wiping my entire MySQL local install and instead setting it on Docker. My company uses MySQL 5.6, which is a generation behind the current 5.7, and navigating between the two locally was a massive headache. The rest of this will still work, if you’re good running MySQL 5.7.
I don’t have much help for you here. I struggled hard on this. Turns out I was using Brew to manage MySQL, and that wasn’t playing nice with other tools, so I threw my hands in the air, and did a clean uninstall/re-install of MySQL on my machine.
I followed these instructions to remove/reinstall.
And, the last setup command in the instructions is deprecated. Where it says to use:
mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
instead use:
mysqld -initialize --verbose --user=whoami --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
(The latter came from a comment in the article.)
In your command line, try to run mysql. If it lets you in, great! if not… read on
Step 1.1 - creating a user for MySQL #
I followed the instructions in this StackOverflow post on creating MySQL users and got it working:
$ mysql -u root
mysql> CREATE USER 'joshthompson'@'localhost' IDENTIFIED BY 'super_secret_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON * . * to 'joshthompson'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
and now I can connect to the DB by running $ mysql -p. It asks for my password, I enter it, and I’m good to go.
Step 2 - import sample date on your machine #
I’m using Sequel Pro at the moment. I think you could do this all from the command line, but since I use Sequel Pro for my real job, I want to build familiarity with the tool. (Despite “pro” in the name, the software is free.)
In Sequel Pro:
name: Local Development
host: 127.0.0.1
username: your_username
password: your_password
click connect
You should now be connected to a database in Sequel Pro, and you’re ready to import some data.
The book includes companion data so the reader can practice the queries mentioned in the book. Here’s the readme for the data. The data itself is in a zip available under the “downloads” section here
In Sequel Pro, go to File > Import, and navigate to the data you downloaded and unzipped.
I started small, and imported everything prefixed with 00. As far as I can tell, all the files prefixed with 00 relate to the schema, but not the actual data. If you import one or all of them, you’ll see a very robust schema, with many tables and keys, but no actual content.
After importing the nine structure files, you might see something like this:
And, after importing the data files:
OK! I’m big on getting friction out of the way, and this is helpful. I’ll not touch the 02 files related to views yet, because i have no idea what those do.
Lets spot check this to make sure it works.
I’ve got the Third Edition of SQL for Mere Mortals, published in 2014. I can pop over to page 98, in chapter 4, and run an example query on a given database, to see if it works.
Here we go…
The query is (in plain english) “Show me the names of all our vendors”.
In sql:
SELECT VendName FROM Vendors;
Easy enough. The only thing to be mindful of seems to be selecting the right database. If the database doesn’t have the Vendors table, the query comes up blank. So I jump into one of the databases with a vendors table, run the above query, and have results! (I ran it against both SalesOrdersExample and SalesOrderModify, and got the same thing.
OK, that does it for now. I’m gonna carry on with my learnings elsewhere, and will update this gist occasionally, as needed.
The rest of my thinking on MySQL will hopefully just be on the actual building and executing queries, both as the relate to this book, and things I’m learning/wrestling with on the job.
Just for fun, and to familiarize myself with the databases and tables, I quickly worked through everything in chapter 4. It’s super basic select stuff from single tables, no joins or groupings or anything.
Onward!