Canadian author and pythonista.

An Order to Learn to Program, Part 3

· Read in about 8 min · (1515 Words)
learning to program beginner sql

Parts in this series

Part 3: SQL Basics

It’s not common to see SQL as the next language taught after HTML. Most authors would jump to a full-fledged structured programming language like Python or Javascript instead. There are a few reasons that I think SQL is a better next choice. While it’s a lot more complicated tool than the formatting languages you’ve been working with, SQL is designed for a specific purpose and is therefore not as overwhelming as a general purpose programming language. It also uses an English-like language that might be more familiar than those other languages. In fact, it was originally designed for use by non-programming business people, although it never achieved that goal.

In addition, for some people, SQL will likely map more easily to concepts you are already familiar with from fields like accounting, inventory management, human resources, etc. SQL is used to interact with relational databases, which are the Marvel hero version of spreadsheets. It’s impossible to learn SQL without also learning about relational databases, and it’s unlikely you would learn about relational databases without also learning SQL. The two go hand in hand. Whichever tutorials you use will certainly teach the concepts together.

I also find that SQL has a very natural order in which it should be taught. Every tutorial out there is going to take on a similar outline. This fits in well with the “start with simple concepts and build on them” layout I’ve been trying to follow in this series of blog articles. The most basic SQL concepts are pretty easy to understand, and lead naturally into somewhat more intimidating territory. Some of the most advanced SQL concepts are pretty intense, but you don’t need to go that far. Once you’ve reached intermediate SQL, I’m hoping you’ll be comfortable enough with the way programming languages are structured that you’ll be comfortable moving on to your first general purpose language.

Finally, all sorts of programming tasks require at least a passing knowledge of SQL. Want to build a web application? SQL. A mobile app? SQL. Data science? Yup. Machine learning? You get the picture. You can’t do any of these things with only SQL, but you also can’t do them without at least a little SQL.

What’s in a name

SQL stands for “Structured Query Language”. In this case, “Query” has the same basic meaning as in English, to ask a question. In particular you will be asking questions of a database. The kinds of questions you might ask include, “Can you put information about this thing into the database?” or “Can you get me information about all the things that have this particular property?”.

The “S” in SQL is the most interesting part. All programming languages are structured; giving the language structure is how the computer knows what to do with it. In the case of relational databases, the structure refers not only to the fact that the language has a specific syntax, but also to the way the data it is querying is stored (conceptually) and accessed.

As an aside, I’ve heard people misuse the “S” as “Standard Query Language”. This is a very adequate description, as there are tons of companies and open source projects making relational database management systems (aka RDBMS), and all of them use the same SQL syntax to query it. SQL is used everywhere in every field, which is why it’s so valuable to know it.

As another aside, SQL can be pronounced as Ess Kue Ell or as in the word “Sequel”. In fact, the original version of the language was named “Structured English QuEry Language”. I personally try to switch between the two pronunciations when I’m talking to people about the subject, just to keep from getting bored.

While I’m defining terms, you should probably know what a relational database is. A database is, unsurprisingly, a place in which you can base your data. Think like a military base, except for data instead of soldiers, I guess. There are a lot of different types of databases, but their general common feature is that they allow you to add and retrieve information in some form that will be useful for some use.

The ‘relational’ part refers to the specific way that data in the database refers or attaches to other data in the system (for example, the way a customer is attached to a specific bank account). You’ll learn more about tables and relationships in the SQL tutorials you choose.

A basic example

Here’s a little taste of what SQL looks like:

sqlite> create table person (firstname varchar, lastname varchar, website varchar);
sqlite> insert into people values ('Dusty', 'Phillips', 'https://dusty.phillips.codes');
sqlite> insert into people values ('Mark', 'Zuckerberg', 'https://facebook.com');
sqlite> select * from people;
Dusty|Phillips|https://dusty.phillips.codes
Mark|Zuckerberg|https://facebook.com
sqlite> select * from people where firstname='Dusty';
Dusty|Phillips|https://dusty.phillips.codes

Learning SQL

I want to stress that SQL is a huge language, and unless it excites you, you shouldn’t learn all the advanced concepts right now. That said, it’ll take you a fair bit longer than your previous studies in Markdown and HTML. You should be able to get quite comfortable with the basic table structure and learn to add, update, and ask questions of data in that database in a couple weeks. More intermediate concepts will probably take twice that long.

After that, you can definitely skip the most advanced concepts. You’ll probably want to get as far as “joining tables”. The “Group By” clause is complicated, so you might skip it, but it’s on the edge. If you’re learning about “table views” and “select..case” statements, that’s great, but those features are definitely more advanced than what I was aiming for. I know that you don’t know what any of these are yet, I’m just giving tips as to where you can drop off your studies if you encounter them.

Unfortunately, I am not aware of any good independent resources for learning SQL. W3Schools as you encountered in Part 2 has a tutorial with an interactive editor similar to the one they used for HTML. However, their tutorial is extremely sparse and may not be very comfortable for a raw beginner. It’s a great reference to remind yourself of the syntax for a specific statement (I do this all the time), but it’s not so great for learning the language the first time.

The independent SQLite Tutorial is another resource worth checking out, but it’s also geared towards people with a bit more knowledge than I expect you to currently have. If it seems too advanced, you’ll likely need to turn to a dedicated course provided by one of the numerous online education providers.

All of online schools out there have introductory SQL tutorials. Some of them offer the courses for free while others charge a fee. Because they all have organizational or profit-based agendas, I am hesitant to publicly recommend any one of them. Different people have different learning methods, and different online schools satisfy those requirements in ways more or less suitable to a specific person.

So I’ll leave you to search for a tutorial that best suits your needs. If there is an online learning platform that you particularly like, check out their SQL courses. Feel free to comment below if you find something you found particularly helpful.

Running SQL locally

By now, you’ve made it far enough into my series that you’re getting serious about these topics. If you haven’t already, it’s time to start setting up a development environment on your own computer, rather than trying to use the limited online editors I’ve been linking you to.

In order to use SQL locally, you have to install a database system and figure out how to connect to it. This can be tricky. I still get frustrated when I have to set up database software on my computer for professional development. It’s so finicky!

Luckily, you probably aren’t doing professional development yet. There is a simple and forgiving database called SQLite. If you visit the page (at least at the time I wrote this), you’ll see that it’s kind of a frightening old-fashioned looking site. But don’t let that scare you off. SQLite is mature and actively developed. This is an exciting phrase in the programming world; it means that the software isn’t going to disappear or become out of date. It’s utilized by a lot of po4pular software, including your web browser.

SQLite can’t be used to build, for example, a website with hundreds of users connected to it at once, but it’s perfect for having a personal database on your laptop or home computer.

By default, SQLite is easiest to run from a command line interface, which you may not be familiar with yet (it’s in a future part of this series). For now, I recommend installing a program like SQLite Studio to easily access your database. The SQLite Tutorial has fairly detailed installation instructions.

Comments