SQL Basics

The Language of Databases

SQL, or Structured Query Language, is the way we communicate with relational databases. Think of a relational database as a collection of organized spreadsheets, called tables. Each table holds data in rows and columns, just like you're used to seeing.

To get information from these tables, or to change the data inside them, you need to speak their language. That language is SQL. Its syntax is like a set of grammar rules for asking questions and giving commands. The commands are usually straightforward English words like SELECT, INSERT, or UPDATE, which makes it relatively easy to learn.

Every SQL command, or query, ends with a semicolon (;). This tells the database that you've finished your instruction.

Defining Your Data

Before you can store anything in a database table, you have to define the structure of its columns. You can't just put text into a column meant for numbers. You must tell the database what kind of information each column will hold. This is where data types come in.

Choosing the right data type ensures that your data is stored correctly and efficiently. For example, storing a year as a number is better than storing it as text. Let's look at a few of the most common data types.

Data Type	Description	Example
`INT`	Whole numbers (integers).	`2024`
`VARCHAR(n)`	Text with a variable length, up to `n` characters.	`'Hello, World!'`
`DECIMAL(p, s)`	Numbers with a fixed precision (`p`) and scale (`s`). Great for currency.	`99.95`
`DATE`	Stores a date (year, month, and day).	`'2024-09-15'`
`BOOLEAN`	Represents true or false values.	`TRUE`

When you create a table, you assign one of these data types to each column. This sets the rules for what can be stored in that column.

Talking to Your Tables

Most interactions with a database boil down to four basic actions, often called CRUD operations:

CRUD

noun

An acronym for Create, Read, Update, and Delete. These are the four basic functions of persistent storage.

Let's see how these actions work in SQL. First, we need to Create a table to hold our data. Imagine we're building a simple library catalog for science fiction books.

CREATE TABLE SciFiBooks (
    book_id INT, 
    title VARCHAR(100), 
    author VARCHAR(100), 
    publication_year INT
);

Now that we have an empty table, let's Create some data by inserting a row into it with the INSERT INTO command.

INSERT INTO SciFiBooks (book_id, title, author, publication_year) 
VALUES (1, 'Dune', 'Frank Herbert', 1965);

What if we made a mistake or need to change something? We can Update existing data. Let's say we want to add a different book to our catalog. We can use the UPDATE command, but we need to be specific about which row to change using a WHERE clause.

UPDATE SciFiBooks
SET title = 'Foundation', author = 'Isaac Asimov', publication_year = 1951
WHERE book_id = 1;

Finally, if we want to remove a row, we use the Delete command. Again, the WHERE clause is crucial to ensure you only delete the row you intend to.

DELETE FROM SciFiBooks 
WHERE book_id = 1;

Asking for Information

The most common action you'll perform is reading data from the database. This is done with the SELECT statement. It's how you ask the database questions about the information it holds.

'SELECT' is the most basic SQL command and the one that anyone who wants to get started in this computer language should learn first.

Let's add a few books to our table so we have something to work with.

INSERT INTO SciFiBooks (book_id, title, author, publication_year) VALUES 
(1, 'Dune', 'Frank Herbert', 1965),
(2, 'Neuromancer', 'William Gibson', 1984),
(3, 'Foundation', 'Isaac Asimov', 1951),
(4, 'Hyperion', 'Dan Simmons', 1989);

To see everything in our SciFiBooks table, we use an asterisk (*) which is shorthand for "all columns".

SELECT * FROM SciFiBooks;

This query would return all four rows and all four columns of our table. But what if we only want the titles and authors? We can specify the columns we want to see.

SELECT title, author FROM SciFiBooks;

This is much more useful. We're only getting the data we asked for. Now, let's get even more specific. We can filter the rows using the WHERE clause. For example, let's find all books published after 1980.

SELECT title, publication_year 
FROM SciFiBooks 
WHERE publication_year > 1980;

This query would return 'Neuromancer' and 'Hyperion'. The WHERE clause can also be used with text. Note that text values must be enclosed in single quotes.

SELECT * 
FROM SciFiBooks 
WHERE author = 'Isaac Asimov';

These basic commands form the foundation of almost everything you'll do with SQL. Mastering them is the first step toward working confidently with data.

Quiz Questions 1/5

What is the primary purpose of Structured Query Language (SQL)?

Quiz Questions 2/5

The WHERE clause is used exclusively to retrieve data with a SELECT statement.

SQL Mastery