Developer Dictionary - SQL

This post is part of a resource called Developer Dictionary. More about why I'm writing this here

Sign-up for new entries

---

A SQL query walks into a bar and sees two tables. It walks up to them and says 'Can I join you?'

There is a lot of data in the world. We need some way to store all of the world's data in computer memory in a systematic way. We use Databases to store and organize data so we can access and make sense of them later. Databases consist of tables. Tables have rows and columns. Multiple tables within a database can be related to each other using keys (column in one table that refers to a column in another table). These are called Relational Databases. SQL, Structured Query Language, is a standard way to communicate with databases — to get data out of the tables or to add new data, as well as to update or delete data.

SQL was initially developed by a couple of people working at IBM in the early 1970s. Then in the late 1970s Oracle developed its own SQL based database. SQL is one of the oldest concepts in technology that has survived through the decades.

So now that we have some context and little bit of history, let's look at some concrete examples of how to use SQL.

SQL Tables

When working with databases, one of the first things we'd do is create a table. The CREATE below is a SQL statement that allows us to define the columns for our table — it's name and type.

CREATE TABLE books (
id int,
title varchar(255),
author varchar(255),
publication_year int,
);

Imagine we have inserted some data into the books table. Each row represents one set of values for the columns mentioned in the CREATE statement.

id	title	author	publication_year
1	The Fellowship of the Ring	J. R. R. Tolkien	1954
2	The Two Towers	J. R. R. Tolkien	1954
3	The Return of the King	J. R. R. Tolkien	1955
4	The Hobbit	J. R. R. Tolkien	1937
5	Animal Farm	George Orwell	1945
6	1984	George Orwell	1949
7	The Alchemist	Paulo Coelho	1988
8	The Hitchhiker's Guide to the Galaxy	Douglas Adams	1979
9	The Restaurant at the End of the Universe	Douglas Adams	1980
10	Life, the Universe and Everything	Douglas Adams	1982
11	So Long, and Thanks for All the Fish	Douglas Adams	1984

SQL Queries

We can read data with SELECT statements. Create new data with INSERT, edit data with UPDATE. And we can delete data with DELETE statement.

SELECT * FROM books
WHERE author = "George Orwell";

This will return all columns from books with id 5 and id 6.

INSERT INTO books (id, title, author, publication_year)
VALUES (12, "Walden", "Henry David Thoreau", "1855");

This will insert a brand new row into the books table. Notice that id of 12. Normally this would be automated. Most Databases have a means to automatically increment ids when new rows are inserted. This ensures that the ids are unique for each row.

We can also update existing values like this:

UPDATE books
SET publication_year = 1985
WHERE id=11;

And delete like this:

DELETE FROM books
WHERE id=5;

Nothing mysterious so far. All straightforward.

What about Joining?

We mentioned that tables in a database can be related to one another using keys. The data in a relational database is stored (aka normalized) across multiple tables. When we need to retrieve some related data across two or more different tables, we join the tables.

Imagine we have another table of quotes that we like from books.

id	book_id	quote
1	5	All animals are equal, but some animals are more equal than others.
2	4	It's a dangerous business, Frodo, going out your door.
3	8	Don't Panic!
4	1	All we have to decide is what to do with the time that is given us.
5	7	There is only one way to learn. It's through action. Everything you need to know you have learned through your journey.

Note the column called book_id. Here is a simple join query that will return all book details for quotes from The Alchemist:

SELECT * FROM books
JOIN quotes ON books.id = quotes.book_id
WHERE books.title = "The Alchemist";

Notice that we're using the book_id column in quotes table to hook into the books table using books.id. We want to get all the quotes we like from the book whose title is "The Alchemist". In this case, we just have one quote with id 5.

There other variations of joins but that's the essence of joining tables in relational databases.

---

And now you know the essence of The Thing called SQL. SQL is not only used by programmers. People working in marketing, sales, business intelligence find that learning SQL benefits their work and so called data-driven decision making also. SQL is more popular than any other language according to 2017 Stackoverflow survey. It has been around for 5 decades, twice as long as JavaScript.