# What is SQL?
SQL stands for Structured Query Language. It is a programming language that we use to interact with Relational Database Management Systems (RDBMS's) by writing queries and sending them to the database.
A query is a small piece of code that tells the database to do something, such as find some data in a table or insert new data. There are many different commands that we can use to build queries to do a wide array of things.
SQL is meant to be similar to the English language: many of the keywords are English words, and the way queries are constructed follows English-like patterns. Here are some examples of SQL queries:
SELECT first_name, surname FROM users;
INSERT INTO users (first_name, surname) VALUES ('John', 'Smith');
SELECT first_name, surname FROM users WHERE surname = 'Smith';
Although these queries are almost self-explanatory, you still need to know how relational databases work in order to truly understand how they will respond.
We'll begin the course by going over the fundamental building blocks of these queries.
# Relational databases
Relational database systems are large and complicated pieces of software that allow us to store and interact with data. Fortunately, we don't have to write the database systems themselves--those have already been written for us! Some examples include PostgreSQL, MySQL, or SQLite.
Most RDBMS's are large, complex, and very powerful. SQLite is a "lite" version that follows the same rules, and is still quite powerful and very flexible. It requires less set-up time, so we're going to use it at the beginning of this course to learn about SQL without having to worry about installing and managing the more complicated PostgreSQL.
Databases are constructed from tables. Each table is composed of one or more named columns (such as first_name
or surname
). Each table has zero or more rows, where each row has a value for each column in the table.
This all may sound obvious, but at the end of the day this is what our users
table in the sample queries above might look like:
first_name | surname |
---|---|
John | Smith |
Rolf | Smith |
Anne | Pun |
Robert | Baratheon |
Some things that may stand out:
- You can have duplicate values; each row is independent (though there are ways to prevent that if you wish).
- Every cell has a value (though there are ways to allow empty values).
- There are two columns. Tables can have more columns than this, but the limit to the number of columns is between 250 and 1600 columns.
# Why "Relational"?
The term "relational" comes from the way tables are often used together. For example, let's say that as well as a users
table, we had a bank_accounts
table, such that it looks like this:
holder | number |
---|---|
Anne Pun | 8375841 |
Robert Baratheon | 1343545 |
John Smith | 9586859 |
Rolf Smith | 1234567 |
You can see how the users
table and the bank_accounts
table are related: bank_accounts
holds the account numbers of people in the users
table.
# Relational tables with unique identifiers
Oftentimes, to simplify these relations, we give each row in each table a unique identifier. For example, our users
table could become this:
id | first_name | surname |
---|---|---|
1 | John | Smith |
2 | Rolf | Smith |
3 | Anne | Pun |
4 | Robert | Baratheon |
And then our bank_accounts
table could become this:
id | holder_id | number |
---|---|---|
1 | 3 | 8375841 |
2 | 4 | 1343545 |
3 | 1 | 9586859 |
4 | 2 | 1234567 |
What this says is that the account with ID 1
is held by the person with ID 3
(Anne Pun).
Through some smart querying we could easily find the person's name associated with each account.
This is where the power of relational databases starts to show. We're going to learn how to harness it throughout the course!
Let's go and learn about those queries we wrote at the top of this chapter, in the next few chapters.