SQL Tutorial: SQL, or Structured Query Language, is a programming language that is nearly indispensable for managing databases in today’s data-driven world.
In a world where data is a valuable resource in its own right, it’s easy to understand why knowing how to use this language is a highly sought-after skill.
In this article, we will briefly introduce how this language works and then delve into the 5 methods you need to know to get started with SQL. Let’s embark on this SQL Tutorial!
💡 Read also:
1) SQL Tutorial: How SQL works
The main advantage of SQL, and probably the reason for its success, is the simplicity of its syntax. While many languages come with strict indentation, specialized vocabulary, and long lines of code, SQL offers methods that fit on a single line and resemble everyday language.
For example: SELECT Name FROM Clients
Literally means “Select names from Clients.”
SQL is based on the system of relational tables. To simplify, you can think of tables as large spreadsheets. The relationships between two tables are common points between two columns in different tables.
For instance, a library manager stores information about customers and books in relational tables. Thus, they use a “Clients” table that contains each customer’s ID, first name, last name, address, and the date of their last library subscription.
In this table, two rows can have the same name, first name, or subscription date. However, the ID has been chosen to be unique. This column is called the primary key of “Clients.”
Primary keys are crucial because they allow us to distinguish between rows. For instance, we can use this key to track the actions of different clients without confusing them.
The manager also uses a “Loans” table, which contains, for each current loan, the ID of the loan and the customer, the book title, the author, and the number of days since the loan date.
We then realize that customer IDs are present in both the “Clients” table and the “Loans” table. As such, we can say that these two tables have a relationship.
Therefore, it is possible for us to execute SQL queries to extract information from one, the other, or both tables simultaneously.
For example, the manager might want to retrieve the first and last names of all customers who borrowed a book more than 3 weeks ago and haven’t returned it, so they can send them a reminder.
2) SQL Tutorial: The 5 most useful methods
Now that you have a general idea of how SQL works, let’s get to the heart of the matter.
In SQL, lines of code are called queries. They consist of methods, which are uppercase words, and attributes, which are usually table or column names written in lowercase
A) CREATE TABLE
The first method you will need to use is CREATE TABLE. Indeed, as the name suggests, it is the syntax for creating a table.
To perform such an operation, you must first specify the name of the table, as well as the name and type of the columns you want to assign to it
B) SELECT … FROM ...
“This is certainly the most commonly used method. It allows us to select a column from a table.
You need to specify the columns you want to select after “SELECT” and which table to search in after “FROM.”
For example, to select the list of first names and last names from the “Clients” table, the syntax is
SELECT First Name (Prénom), Family Name (Nom) FROM Clients
Note that we often see * after SELECT. This symbol means “all,” in other words, if you want to select the entire “Clients” table, you would write:
SELECT * FROM Clients
C) WHERE
As we’ve seen, SELECT allows you to select all rows in a column. WHERE allows you to specify which rows you want to select or not within that column.
So, you use WHERE after a SELECT … FROM … statement, specifying the condition for selecting one row over another.
For example, to select the subscription date of clients with the name “Dupond,” the syntax is:
SELECT Date_Souscription FROM Clients WHERE Nom = Dupond
D) FULL JOIN … ON ...
Now that we know how to precisely select columns and rows from a table, let’s see how to retrieve information from two tables simultaneously.
We previously mentioned that the Clients and Emprunts tables have a relationship because they both contain a column with client IDs.
So, it’s possible to merge these two tables into one, incorporating all the information from the two previous tables.
To do this, you can use the FULL JOIN … ON … method. This is used after a SELECT … FROM … statement. First, you specify the two tables to join, then the columns that have a relationship.
For example, to select all columns from the merge between Clients and Emprunts, the syntax is:
SELECT *
FROM Clients FULL JOIN Emprunts
ON Clients.Clients_id = Emprunts.Clients_id
We will refer to this new table as Fusion from now on.
Since both tables have identical column names, it’s necessary to specify the table name before the column name. For example, Clients.Clients_id represents the Clients_id column of the Clients table.
It’s also noticeable that some cells contain ‘NaN,’ which means missing values. For example, Isabelle Dupond doesn’t have any current loans.
There are other types of joins (LEFT JOIN, RIGHT JOIN, INNER JOIN) that allow you to keep or discard these types of problematic rows
E) GROUP BY
We now know how to merge multiple tables, but sometimes it’s useful to do the opposite and reduce a table to keep only essential information. For example, it might be better to display an average rather than a large number of rows for readability.
GROUP BY is used after a SELECT… FROM… statement. We then specify the name of the column on which we want to group the data. However, to perform an operation like an average, a sum… it’s necessary to specify it after the SELECT.
For example, to select the number of days since the oldest loan date for each library client, the syntax is:
SELECT Clients_id, MAX(Duration) FROM Fusion GROUP BY Clients_id
We have completed the top 5 most useful SQL methods in our SQL Tutorial.
You now have the keys to getting started with SQL. If you’re interested in this language and want to learn more, discover our courses:
