An Introduction to Relational Databases and SQL

The term “databases” has filtered through to the common consciousness, from police procedurals where characters search through national crime databases to frequent news stories about database breaches exposing our personal information. Learning about databases is important to better reframe the news, to understand the capabilities available to you on your phone and at work. SQL, the language used to access stored data, is important to have a basic understanding in order to speak the language of techies and to demystify the workings of databases behind web pages and on our company services.

This blog post covers the basics and provides references for further exploration.

What are databases?

Most simply, relational databases store information in table form. An example, listing some well-regarded cooks:

This table contains information about five cooks; the first row — the one which shows the column names — is an aid to the viewer. Each column contains data of the same type, like a cook’s first name. Each row contains all the data relating to one cook, a digital version of one card in a Rolodex or one entry in a little black book.

The majesty of a digital database isn’t the novelty in collecting data, something done since the advent of Cuneiform writing, but in the correlating of that data.

The practical birth of the row of data can be traced back to the challenge of timely tabulation of the results of the 1890 U.S. census. This problem was solved by recording information related to one person onto a punched card. These cards were then counted and summarized mechanically, with less error than human tabulators. This effort, government and then commercial data processing, evolved into today’s IBM company.

What is SQL?

SQL (Structured Query Language) — pronounced “sequel” or “ess queue ell” — is a computer programming language tailored to interacting with data stored in relational databases. SQL provides the essential methods to operate on sets of data, known as “CRUD” (create, read, update, and delete). Here’s the SQL needed to create the table of cooks:

CREATE TABLE cooks (
firstnameVARCHAR(20),
lastnameVARCHAR(30),
bornDECIMAL
);

The table name — cooks — is required and the name and data type of each column is specified. VARCHAR(20) means “text field with a maximum width of 20 characters” and DECIMAL means “integer number in base ten” (also known as “counting numbers”). A deeper look into the available data types and SQL language constructs may be found at the Udacity SQL Language Tutorial.

The SQL needed to populate the table:

INSERT INTO cooks
VALUES
( "Auguste", "Escoffier", 1846 ),
( "Joël", "Robuchon", 1945 ),
( "Éric", "Ripert", 1965 ),
( "Hélène", "Darroze", 1967 ),
( "Marie-Antoine", "Carême", 1784 );

The SQL used to retrieve the table shown above is:

SELECT *
FROM cooks
ORDER BY born ASC ;

This means “select everything from the cooks table and sort the results by the cook’s year of birth, oldest first.”

To permanently delete all cooks born before the twentieth century:

DELETE
FROM cooks
WHERE born < 1900 ;

The born < 1900 demonstrates a “WHERE clause” which constrains the deletion action, as well as the use of the less-than arithmetic operator.

Lastly, please note that the cooks’ names in the table include diacritics, as in the accent aigu in Éric. Modern relational databases support the internationalization and language localization needed by modern apps to represent real-world people and places.

Conclusion

Covered here is a cursory introduction to relational databases, their function and history, and a brief introduction with the SQL programming language. Further readings include a deeper Udacity SQL Language Tutorial a topic-by-topic list of Udacity Hub of SQL Language Topics.

Start Learning

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

SQL Sample Table Code

The following will build the code sample tables referred to in this blog post. Examine them interactively at  SQL Fiddle or CodingGround.

CREATE TABLE cooks (
firstnameVARCHAR(20),
lastnameVARCHAR(30),
bornDECIMAL
);
INSERT INTO cooks
VALUES
( "Auguste", "Escoffier", 1846 ),
( "Joël", "Robuchon", 1945 ),
( "Éric", "Ripert", 1965 ),
( "Hélène", "Darroze", 1967 ),
( "Marie-Antoine", "Carême", 1784 );