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:

• Create

  Putting data into tables.

• Read

  Query data out of a table.

• Update

  Change data already in a table.

• Delete

  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.

The Setup

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.

CREATE

Creates a database table that can store columns of data.

INSERT

Adds rows to a database table, filling in the columns of data.

UPDATE

Changes one or more columns in a table.

DELETE

Deletes a row from a table.

SELECT

Queries a table, or set of tables, and returns a temporary table with the results.

DROP

Destroys a table.

FROM

Frequently part of a SQL query to specify what tables or columns to use.

IN

Used to indicate a set of elements.

WHERE

Used with queries to say where something should come from.

SET

Used with update to indicate what columns to set to what.

SQL Grammar

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.

Further Study

1. 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.
2. Drill these cards for the entire time that you're doing the remaining exercises.