This book is new. If you'd like to go through it, then join the Learn Code Forum to get help while you attempt it. I'm also looking for feedback on the difficulty of the projects. If you don't like public forums, then email firstname.lastname@example.org to talk privately.
Exercise 38: Introduction To SQL
The best way to learn how to model and design solid data is to start with the very basic building blocks. The SQL ("sequel") style of database has been the standard for data modeling and storage for many decades. Once you know basic SQL you can easily use just about any NoSQL or object relational mapping (ORM) system out there. SQL is a very formal way to store, manipulate and access data that gives you a formal way to think about it. It's also not very difficult since the language is not Turing complete like a full programming language.
SQL is everywhere, and I'm not saying that because I want you to use it. It's just a fact. I bet you have some in your pocket right now. All Android Phones and iPhones have easy access to a SQL database called SQLite, and many applications on your phone use it directly. It runs banks, hospitals, universities, governments, small businesses, and large ones; just about every computer and every person on the planet eventually touches something running SQL. SQL is an incredibly successful and solid technology.
The problem with SQL is it seems everyone hates its guts. It is a weird obtuse kind of "non-language" that most programmers can't stand. It was designed long before any of these modern problems like "web scale" or object-oriented programming even existed. Despite being based on a solid mathematically built theory of operation, it gets enough wrong to be annoying. Trees? Nested objects and parent child relationships? SQL just laughs in your face and gives you a massive flat table saying, "You figure it out bro."
Why should you learn SQL if everyone hates it so much? Because behind this supposed hate is a lack of understanding of what SQL is and how to use it. The NoSQL movement is partially a reaction to antiquated database servers and also a response to a fear of SQL borne from ignorance of how it works. By learning SQL, you actually will learn important theoretical concepts that apply to nearly every data storage system past and present.
No matter what the SQL haters claim, you should learn SQL because it is everywhere, and it's actually not that hard to learn enough to be educated about it. Becoming an educated SQL user will help you make informed decisions about what databases to use, whether to not use SQL, and give you a deeper understanding of many of the systems you work with as a programmer.
What Is SQL?
I pronounce SQL "sequel" but you can also say "ess-queue-ell" if you want. SQL also stands for Structured Query Language, but by now nobody even cares about that since that was just a marketing ploy anyway. What SQL does is give you a language for interacting with data in a database. It's advantage, though, is that it closely matches a theory established many years ago defining properties of well-structured data. It's not exactly the same (which some detractors lament), but it's close enough to be useful.
How SQL works is that it understands fields that are in tables and how to find the data in the tables based on the contents of the fields. All SQL operations are then one of four general things you do to tables:
- Putting data into tables.
- Query data out of a table.
- Change data already in a table.
- Remove data from the table.
This has been given the acronym "CRUD" and is considered a fundamental set of features every data storage system must have. In fact, if you can't do one of these four in some way then there better be a very good reason.
One way I like to explain how SQL works is by comparing it to a spreadsheet software like Excel:
- A database is a whole spreadsheet file.
- A table is a tab/sheet in the spreadsheet, with each one being given a name.
- A column is a column in both.
- A row is a row in both.
- SQL then gives you a language for doing CRUD operations on these to produce new tables or alter existing ones.
The last item is significant, and not understanding this causes people problems. SQL only knows tables, and every operation produces tables. It either "produces" a table by modifying an existing one, or it returns a new temporary table as your data set.
As you read this book, you'll begin to understand the significance of this design. For example, one of the reasons object-oriented languages are mismatched with SQL databases is that OOP languages are organized around graphs, but SQL wants to only return tables. Since it's possible to map nearly any graph to a table and vice-versa this works, but it puts the burden on the OOP language to do the translation. If SQL returned a nested data structure, then this wouldn't be a problem.
We will use SQLite3 as a training tool for this section. SQLite3 is a complete database system that has the advantage of requiring almost no setup. You just download a binary and work it like most other scripting languages. Using this, you'll be able to learn SQL without getting stuck in the administrivia of administering a database server.
Installing SQLite3 is easy:
- Either go to the SQLite3 download page and grab the binary for your platform. Look for "Precompiled Binaries for X" with X being your operating system of choice.
- Or use your operating system's package manager to install it. If you're on Linux then you know what that means. If you're on macOS then first go get a package manager, and then use it to install SQLite3.
When you've got it installed, then make sure you can start up a command line and run it. Here's a quick test for you to try:
$ sqlite3 test.db SQLite version 3.7.8 2011-09-19 14:49:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table test (id); sqlite> .quit
Then look to see that the test.db file is there. If that works, then you're all set. You should make sure that your version of SQLite3 is the same as the one I have here: 3.7.8. Sometimes things won't work right with older versions.
Learning SQL Vocabulary
To get started learning SQL you'll want to create flash cards (or use Anki) for these SQL terms. In the exercises after this one you'll learn and apply each of these SQL words to different problems. The best way to think about the SQL language is everything ends up being a Create, Read, Update, and Delete operation. Even if a word is INSERT you'll still think of that as a Create operation because it will create data. At first, just spend some time memorizing these words and keep drilling them as you do the exercises for this section.
- Creates a database table that can store columns of data.
- Adds rows to a database table, filling in the columns of data.
- Changes one or more columns in a table.
- Deletes a row from a table.
- Queries a table, or set of tables, and returns a temporary table with the results.
- Destroys a table.
- Frequently part of a SQL query to specify what tables or columns to use.
- Used to indicate a set of elements.
- Used with queries to say where something should come from.
- Used with update to indicate what columns to set to what.
You'll next want to make another set of cards for the important grammar constructs of SQL. There's not too many of them, but write these up (or use Anki) and start drilling them now so that you learn the language faster. The grammar you're learning is the one for the SQLite3 database we'll be using in this book. It's a fairly common SQL grammar, but each database has different little weird flavors that you'd have to learn. Once you learn this one it's easy to figure out what another database uses.
You'll want to visit the SQLite 3 definition page to create the cards you need. This page lists out everything that SQLite understands, but just focus on the main statements I list above. Add any other words you do not understand. Their diagrams are a little complicated, but they are simply graphical views of the SQL BNF which you learned about in Part V. If you don't remember ABNF go back through Part V and relearn it.
- Go to the SQLite3 grammar list and browse through all of the available commands. Most of them won't make sense, but if any interest you then make cards for those as well.
- Drill these cards for the entire time that you're doing the remaining exercises.