SQL From Scratch Part 1 - Getting Started with SQL

Welcome to the first article of what will hopefully be a pretty long and useful series on SQL.

Before I dive into writing SQL, I want to make sure everyone is one the same page. Let's set the stage for this topic.

Just a heads-up, I'm assuming a very small amount of knowledge about your computer's terminal in this article, but feel free to drop questions in the comments if you get stuck.

Why learn SQL?

@dabit3 recently pointed out that most web apps are basically lists and forms (and you can build a pretty solid career around making those lists and forms).

But what happens to the data submitted in those forms, and where does the content that populates those lists come from? Nader hinted at the answer: Usually it's an API that talks to a database.

SQL is the language of databases. If you want to understand what you're hooking those lists and forms into, what they represent to the application, SQL is the best tool for exploration and understanding.

These days direct knowledge of SQL isn't strictly necessary for writing software backed by a database. We can build web apps with Rails leaning on an ORM so that we never even see SQL, but that is a surefire way to build inefficient applications.

If you're not a web developer learning SQL might seem less interesting, but SQL databases aren't used exclusively in web apps. SQL is used all over your operating system, it is used to build mobile apps, and if you have any interested in data science or business analysis, SQL is a necessary tool.

Even folks with less technical focus can benefit heavily from learning SQL. If you were a product manager looking to figure how many users sign up for your application each day, would it be easier to ask a developer to write a query for you or spend a few seconds typing up a query? If you wanted to figure out how many of those users were on the west coast of Japan? Basic knowledge of SQL could help you answer those questions and many more similar questions in a few seconds.

Want more data? SQL is everywhere, it shows up every single year on StackOverflow's Developer Survey.

Most popular programming languages according to StackOverflow:

2019

2018

2017

2016

2013

If SQL is running all over the place, most developers should have a grasp on the basics. It's a marketable skill and a super useful one, so a better question might be "why not learn SQL?"

What is SQL?

Structured Query Language (SQL) is a programming language that dates back to the 70s, but it is still in wide use today. The site you're using right now is running SQL queries to show you the content you're browsing.

Developers use SQL to interact with databases. If you've built websites using Rails or Django, you're probably familiar with using Ruby or Python to interact with a database, but in reality your application is generating SQL based on the code you wrote in Ruby or Python.

When we write SQL we are usually trying to accomplish one of four "CRUD" actions, or we are directly administrating a database. We'll talk more about CRUD later, but you should know that CRUD actions are all about manipulating data. For example, we might store sales records in our database and need to change a record to reflect a refund or discount. We might want to figure out how many of our customers bought Christmas trees in August. Those are tasks we can tackle with SQL.

SQL is largely unique from the modern programming languages we use to build websites, mobile apps, and video games (C#, JavaScript, Ruby, Python, etc).

We can describe one of the biggest distinctions between SQL and a language like Ruby in one word: Declarative. SQL is a "declarative" programming language. In layman's terms, this means that when we write SQL we don't typically need to describe how our code runs, rather we just declare what we want to happen.

Here is some SQL-like pseudo-code that might clarify the idea:

# We want a list of Twitter usernames
# for all the user accounts on DEV

Show me every DEV user's Twitter username
From the database's user table
Where the user has linked a Twitter account 

If you've seen SQL before, you might notice some similarities between this pseudo-code and a real-world query that would return a similar dataset:

SELECT twitter_username
FROM users
WHERE twitter_username IS NOT NULL

This SQL doesn't look much like the code we write in JavaScript or Python. That's probably because in those languages we'd have to tell the programming language a lot more about retrieving information from the computer's memory. In SQL, we just tell the computer what information we want, not how to retrieve that information.

There is a lot of information online about the history and development of SQL, but I think this is enough information the conversation rolling.

Setting up a SQL playground

If we want to learn SQL, we'll need an environment to write SQL and some data to explore. We'll need a database.

There are a lot of tools out there for creating and interacting with databases. You've probably heard of a few (SQL Server, PostgreSQL, MySQL, CockroachDB, SQLite) and you might have a favorite, but you shouldn't spend too much time worrying about that!

Each database provider is going to be a bit different, but generally the things you learn on one kind of SQL database is going to work on the others.

For the purposes of these articles, I might jump between several environments, but a lot of my examples will come from the DEV database. DEV uses PostgreSQL which can be quite a bit of work to set up locally, so I don't recommend jumping down that rabbit hole if you are just looking to get a feel for SQL.

I recommend setting up SQLite for your SQL learning environment. It's easy to get started, easy to fix when something goes sideways, and doesn't require much of your system.

If you're on Ubuntu, like me, you can install SQLite3 with this command:

sudo apt-get install sqlite3

If you're on MacOS, I'd probably go with the brew option:

brew install sqlite3

For Windows users, it is a bit more complicated, and I'm not a Windows user so you'll have to flex your Search Engine muscles here. If you're a Windows user and have installed SQLite before, feel free to drop some links or guides in the comments (I might include the comment in this part of the article later).

You can test your SQLite installation by running sqlite3 in the terminal. It should look something like this:

The next step for setting up your playground is a dummy dataset. There are a lot of datasets available online for learning, but a really common one called "Northwind" is easy to get running with SQLite.

You'll want to create a new directory on your machine for the database, then go grab the Northwind script from GitHub.

I think it's easiest to grab this file with cURL and pipe it into a file:

# Go check the URL in your browser before you blindly cURL something!
curl -LJ0 https://raw.githubusercontent.com/jpwhite3/northwind-SQLite3/master/Northwind.Sqlite3.create.sql > nw.sql

If that looks foreign to you, don't worry too much. This is grabbing the file from a repo on GitHub and putting it into a file in your current directory called nw.sql.

Once we have the file on our machine, we'll make a new database and run the script to populate it with dummy data.

# Create the database
sqlite3 Northwind.db

# You'll be dropped into the database's command line interface
# Run the contents of nw.sql with the .read command
.read nw.sql

If you followed the steps correctly, you should see your console spitting out a lot of information (hopefully no errors)!

When the console slows down and you see the sqlite> prompt again, try typing (not pasting) this simple query in and see what happens:

# Don't forget the semi-colon!

SELECT ContactName FROM customers LIMIT 5;

Before you hit enter, try to imagine what this will do. It's really similar to English, so you might get closer than you think.

Maria Anders
Ana Trujillo
Antonio Moreno
Thomas Hardy
Christina Berglund

This query returns the names of 5 customers from our customers table.

If you see something similar to my results in your console, you've got SQLite and your Northwind dataset ready to go!

This should give you an environment that is safe to explore SQL without fear of causing damage to a real world database. You can also delete your Northwind.db file and recreate this database at any time by following the steps again, so don't worry about breaking anything!

In the next article we'll set up a tool to make visualizing our database much easier, we'll define some database words (tables, rows, and columns galore), and we'll talk about writing a query from scratch.

Bonus

If you want to explore the Northwind dataset a bit more in your terminal, you can use the .tables command to ask SQLite for a list of tables in your database.

See if you can figure out how to get a 5 employee's first names. I'll drop the answer in the next article.

There's more...

I'm writing a lot of articles these days, I run a podcast, and I've started sending out a newsletter digest about all of the awesome stories I'm hearing.

You can also follow me on Twitter, where I make silly memes and talk about being a developer.