Today I tried to write a SQL Query on paper without the databases. It didn’t work.

For the finale of part 2/4 of the Udacity Relational Databases course, I used three tables to perform a count of all the animals at a zoo. Next, I displayed the animals in a table using their common names and sorted the rows by animal popularity. I definitely did some overthinking during the early stages. By the end I had to laugh at how easy it was to find the solution.

I had to join three tables: animals, ordernames, and taxonomy. To join the tables, I looked through the tables for columns with similar data.
I opened three tabs and queried each table in a respective tab. This allowed me to look at each table at the same time.

I noticed that even though each table had a name column, their contents did not correspond. Animal names were pet names, taxonomy names were species of animals, and ordernames names were common names of animals. I instantly realized this was why my inital join using each table’s name column failed. Looking closely at the tables, I spotted a pattern.

Animal species and taxonomy names had plenty of matches, taxonomy t_order and ordernames t_order also had matching content. I joined animals, taxonomy, and ordernames on animals.species = taxonomy.name and taxonomy.t_order = ordernames.t_order. The rest of the query was simply manipulating the information to display as prompted.

Writing the query without looking at the data was like doing mental math. It’s way easier with pen and paper (or in this case, a screen). Funny thing is when I’m coding in Java, I always keep my pseudocode handy and work on each step one-by-one. It’s way easier to build with a blueprint. In the future, I’ll be sure to keep information readily available when writing queries as well.