SQL 101 – The Basics

Last Updated on September 4, 2024

Introduction

First things first, a little bit of history for us to understand what is SQL. Structured Query Language (SQL) is a domain-specific language used in programming and developed in the 70s by IBM to implement the relational model proposed by E. F. Codd. The language becomes a reference to databases with simple commands and is easy to use. When you use SQL, you are ‘querying’ the data, communicating with the data stored in RDBMS.

RDBMS stands for Relational Database Management System, which is software that allows you to store and manipulate data (create, read, update and delete). Most RDBMS use SQL as their query language. Some of the most popular RDBMS flavors are MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and SQLite, each one with its own singularities. MySQL and PostgreSQL are extremely popular, 46.85% and 43.59% of 63327 responses, according with 2022 Developer Survey from Stack Overflow.

But why do we need to learn SQL? Well, in the first place, almost every application that has a relational database uses SQL, and unlike learning a hardcore programming language, SQL is quite simple to learn because SQL query syntax relies on common English words. So, if you know a little bit of English, you won’t have so many problems learning SQL.

Also, it has some advantages, such as high speed to provide a quick and efficient way to retrieve, manipulate or store data; well-defined standards so you can find documentation and troubleshooting guidance, easy to integrate with other programming languages, and the best of all, it’s an open-source programming language. SQL data types can be divided into categories. Before we start, let’s check those categories and their data types (Image 1):

Image 1 – SQL Datatypes
Source: https://www.digitalocean.com/community/tutorials/sql-data-types#sql-data-types

Note that each RDBMS has its own data types, so you may find some differences between them. Also, if you want to go deeper into data types, you can check it here.

Commands

There are many commands in SQL, in this post we are going to focus on the most used commands. Let’s quickly go over them:

SELECT
WHERE
INSERT INTO
UPDATE
DELETE
CREATE TABLE

SELECT statement retrieves data from a database. The result is exposed in a result table, which can be called a result-set.

WHERE clause is mainly used to filter the data being querying. Extracting only records that passes in a specified condition.

INSERT INTO statement is mainly used to save new data in specific table columns.

UPDATE statement is used to change the existing records in table columns.

DELETE statement is used to delete data in a table with a specific condition.

CREATE TABLE statement, as you might think, is to create new tables in a database, specifying the column name and attributes that it will have.

For more detailed information about each command, you can check the links above.

Examples

We have explained a little about SQL and which are the most used commands, but how to use it? Let’s find out. Before anything, we need a table, right? Let’s make one, using one of those commands, and work with that.

CREATE TABLE posts (id int, title varchar(255), content text);

This command will generate something like this:

Now that we have a table, it is possible to populate it and do some work with that data.

INSERT INTO posts(id, title, content)
VALUES (1, "SQl 101", "Basics of SQL content");

As we saw earlier, this command saves new data in a specific table, assuming we have done that multiple times, the table should be something like this.

See how easy it is? You probably might be asking yourself: ‘Ok. I have a table, but what can I do with it? How do I retrieve data?’ We will see this right now.

SELECT * FROM posts;

This is the simplest way to get all data from a specific table (see that we use *, which mean that we want all data) since we do not specify anything, no columns or give a specific condition to it. So the output of this command is basically the same table we showed before. But if we only need it to retrieve the titles from our table? Let’s see.

SELECT title FROM posts;

Now we specify the column title, our result table should be this:

You already noticed the use of SELECT command, right? We can specify not just one, but any column that exists in the table. Now that we know what this command does, let’s look at some conditional ways to retrieve data.

SELECT title, content FROM title WHERE id > 1;

The result set is:

There are only two commands left for us explore, let’s check them out.

UPDATE posts
SET title = "SQL 101 UPDATED"
WHERE id = 1;

We have updated the title for the data with id = 1. It is easy, right !?

But, what about DELETE? Let’s check it.

DELETE FROM posts WHERE id = 3;

We have specified id = 3, so it should delete only data that match this condition. So we will have this table after using DELETE:

Just a friendly reminder, always use WHERE clause when using DELETE command, otherwise, you will delete all data from the table.

Conclusion

This is it folks! In this post we covered the basics of SQL and how to use basic commands. Also, this post is the first one in a series we’re making. In the next one, we will discuss relationships between tables and how to correlate data. See you soon, and thank you for reading!