SQL: Essentials

Okay, in this article we will look at the basic concepts needed to write your first SQL queries.

If you are still in doubt about what SQL is I recommend you read this article first.

SQL SELECT

Let’s start with the most basic and simple, SQL SELECT.

This command is used to select data from a database. Take a look at the example below.

SELECT column1, column2, ...
FROM table_name;

Might not be necessary, but we can ‘translate’ this as: “Select the columns ‘column1’ and ‘column2’ from the table ‘table_name’.

Here, column1, column2, must be the field names of the table from which you want to select data. If you want to select all the available fields in the table, use the following syntax:

SELECT * FROM table_name;

As soon as you run this command, the data returned is stored in a result table, called the result set.

I think I can show this and the next examples better using table. So let’s create one for practice.

The table we will use is as follows. Table “clients”.

id	name	address	city	country
1	Rodrigo Nascimento	Berguvsvägen 8	Berlin	Germany
2	Marcio Moreno	Av. Brasil 222	Rio de Janeiro	Braszil
3	Antonio Pereira	Av. Paulista 321	São Paulo	Brazil
4	Clovis Oliveira	P. Sherman 42, Wallaby Way	Sydney	Australia
5	Helder Santos	120 Hanover Sq.	Brisbane	Australia

The following SQL statement selects the “name” and “city” columns from the “clients” table:

SELECT name, city FROM clients;

And your set of results should look like this:

id	name	city
1	Rodrigo Nascimento	Berlin
2	Marcio Moreno	Rio de Janeiro
3	Antonio Pereira	São Paulo
4	Clovis Oliveira	Sydney
5	Helder Santos	Brisbane

SQL WHERE

As we have just seen, with SELECT you can filter the columns, right? Now let’s say you want to filter some of the elements in each row.

For example, you want to know which customers live in Brazil or Australia. Using just SELECT this is not possible.

For that we need a WHERE.

The SQL WHERE clause is used to specify a condition when retrieving data from a single table or joining multiple tables.

If the given condition is satisfied, it returns all records that match the condition.

The goal is to use the WHERE clause to filter the records and fetch only the records you need.

For the example above, we could write a query like this:

SELECT name, city FROM clients WHERE country = 'Brazil';

id	name	city
2	Marcio Moreno	Rio de Janeiro
3	Antonio Pereira	São Paulo

Ok, one thing you need to keep in mind, SQL requires single quotes around text values like in ‘Brazil’ up there (most DBMS will also accept double quotes as well).

However, for numeric fields you should not put quotes around them:

// Wrong
SELECT name, city FROM clients WHERE id = '1';

//Correct
SELECT name, city FROM clients WHERE id = 1;

SQL LIKE

Nothing more convenient than to continue this list with the LIKE operator, as it is used in a WHERE clause to look for a specified pattern in a column.

I know, better with an example, right?

Let’s say we need to update the customer’s address in our database.

Let’s say that we need change every Brazilian address that has the abbreviation “Av” to “Avenida”(Avenue), to help with delivery logistics.

But before this update the manager needs to see how many customers will be affected by this change.

Since “Avenue” or “Av” are not columns in our table, we can’t use just the WHERE.

For this we need to use LIKE, the query looks like this:

SELECT name, address FROM clients
WHERE address LIKE 'Av%';

id	name	address
2	Marcio Moreno	Av. Brasil 222
3	Antonio Pereira	Av. Paulista 321

You might be wondering what the hell this “%” is doing there, or that I made a typo, but no, it’s all right.

There are two wildcards often used in conjunction with the LIKE operator:

• The percent sign (%), which can represent zero, one, or multiple characters.
• The underscore sign (_), which represents a single character.

There are many possible combinations, but I will list those that I consider to be the most important.

Operador LIKE	Descrição
WHERE column LIKE ‘a%’	Find any values that start with “a”.
WHERE column LIKE ‘%a’	Find any values ending with “a”.
WHERE column LIKE ‘%or%’	Find any values that have “or” in any position
WHERE column LIKE ‘_r%’	Find any values that have “r” in the second position
WHERE column LIKE ‘a_%’	Find any values that start with “a” and are at least 2 characters long
WHERE column LIKE ‘a__%’	Find any values that start with “a” and are at least 3 characters long
WHERE column LIKE ‘a%o’	It finds any values starting with “a” and ending with “o”.

So in our example, I’m using the first command in this table. And it should return me customers with id = 2 and id = 3.

But if you want, you can be very specific, and use LIKE without any wildcard. Like this:

SELECT * FROM clients
WHERE address LIKE 'Av. Brazil 222'; 

SELECT * FROM clients
WHERE address LIKE 'Av. Paulista 321';

We can still upgrade this query, instead of running two queries, we can run only one query that will solve our problems. Our next operators are here for that.

Operators AND, OR e NOT

Now we can combine the WHERE clause with the AND, OR, and NOT operators, and simplify the query.

And to be clear, AND and OR are used to filter records based on more than one condition, just like our example above.

And NOT to negate a condition, that is, anything that is NOT equal to the condition.

Using these operators, you will write your queries using this syntax:

//AND
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

//OR
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

//NOT
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

In the table below is a brief description of what each operator does.

Operator	Description
AND	Displays a record if all conditions separated by AND are TRUE.
OR	Displays a record if any of the conditions separated by OR are TRUE.
NOT	Displays a record if the condition(s) is NOT TRUE.

So, as I said before, we can simplify the query in our last example, instead of using two separate queries, we can write just one, using one of the operators above, in this case, we will use OR.

After simplifying our query the result is this:

SELECT name, address FROM clients
WHERE (address LIKE 'Av. Brazil 222' or address LIKE 'Av. Paulista 321');

id	name	address
2	Marcio Moreno	Av. Brasil 222
3	Antonio Pereira	Av. Paulista 321

SQL UPDATE

Now we have two methods for selecting and viewing data. But usually we want to do something with this data, right?

For example, let’s say one of our customers has moved city or maybe even country, so the fields “address”, “city” and “country” will change, and we need to update this customer’s record. How to do it?

Well, the first step is to select the Customer that is moving, right? And we already know how to do that, using SELECT and WHERE, but this only doesn’t help, we need an extra thing.

Let’s use the UPDATE method, that does exactly the same thing, with an extra feature, we will update the values at the same time. Like this:

UPDATE clients
SET city = 'Paris', country = 'Franca', address = '60 rue La Boétie'
WHERE id = 1;

id	name	address	city	country
1	Rodrigo Nascimento	60 rue La Boétie	Paris	Franca

Here we have the UPDATE command instead of SELECT. And right after we have the line with the SET command (or keyword), it is used with the UPDATE command to specify which columns and values should be updated in a table.

So, what we are doing here is simple, let’s translate this command.
“Update within the ‘customers’ table the ‘city’, ‘country’ and ‘address’ of the customer whose id equals 1”.

Simple, isn’t it? We just sent Rodrigo to France.

Now, attention!

Be careful when updating records in a table! Note the WHERE statement in the UPDATE statement. The WHERE clause specifies which record(s) should be updated. If you omit the WHERE clause, all records in the table will be updated!

SQL DELETE

Everyone, at one time or another in life does a little 💩 right?

In the case of the database, if you have saved a record that you shouldn’t, and you need to remove it, this is the method you will use.

Let’s imagine this scenario, that the customer Clovis no longer wants to be part of our customer club. By company policy, all of that customer’s data needs to be deleted.

This is the cue to use DELETE.

In the example, it would look like this:

DELETE FROM clients WHERE name = 'Clovis Oliveira';

id	name	address	city	country
1	Rodrigo Nascimento	Berguvsvägen 8	Berlin	Alemanha
2	Marcio Moreno	Av. Brasil 222	Rio de Janeiro	Brasil
3	Antonio Pereira	Av. Paulista 321	São Paulo	Brasil
5	Helder Santos	120 Hanover Sq.	Brisbane	Australia

And that’s it, that line will be completely deleted from the database.

But beware!

If you don’t provide a WHERE in the clause with DELETE, the entire table data will be deleted!

!!! THIS WILL ERASE ALL THE DATA FROM THIS TABLE!!!
DELETE from clients;

Only run this command if you are sure of what you are doing. Preferably only run it on a test database.

SQL Joins

Structured Query Language (SQL) is a programming language that is used in relational databases. And by “relational” I mean that data between tables has some kind of relationship with each other. And to handle many of these cases, the commands we have seen so far are insufficient. Let’s expand our SQL vocabulary with JOINS.

Undoubtedly one of the most important SQL clauses, let’s understand what it does and how it works.

The JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Ok, to better explain what this command does we will need some examples.

For these examples we will need a second table, which we will call “orders”.

ID	client_id	total	due_date
1	2	$ 342	12/08/2022
2	4	$ 629	04/11/2022

Note that the “customer_id” column in the “orders” table refers to the “id” in the “customers” table.

From this we can extract our relationship, because when we talk about a customer we want to know if he has any orders, and when we look at the list of orders, we need to know who that order belongs to.

JOIN solves this problem for us, and the result is shown in the table below.

SELECT orders.total, orders.due_date, clients.name
FROM orders
INNER JOIN clients ON orders.client_id=clients.id;

total	due_date	name
$ 342	12/08/2022	Marcio Moreno
$ 629	04/11/2022	Clovis Oliveira

Ok, it is not the simplest of queries, but it is easy to understand. Let’s translate.

“Select the “total” and “due_date” columns in the “orders” table and the “name” column in the “clients” table from the “orders” table by merging with the “clients” table where the “client_id” column in the “orders” table is equal to the “id” in the “client” table.”

Still confusing? let’s break it down into parts, to see if it helps.

The first part we have already seen in the SELECT example. Very similar

Select the “total” and “due_date” columns in the “orders” table and the “name” column in the “clients” table from the “orders” table.

SELECT orders.total, orders.due_date, clients.name
FROM orders

Okay, we have two things to point out here, one is that we are using “orders.total” instead of just “total”. The other is that we have “clients.name” which has nothing to do with the orders table.

That’s the magic of the JOIN command, it helps you join columns from several tables into a single result.

We have the columns “due_date” and “total” coming from the order table and “name” coming from the “customers” table, so we need to specify where each piece of data is coming from.

We do this using the notation: table.column

Now the novelty is the second part:

INNER JOIN clients ON orders.client_id=clients.id;

Which translates as something like this:

“Make union with table “clients” where column “client_id” in table “orders” is equal to “id” in table “clients”.”

The summary then, when “client.id” and “order.client_id “match, show me those three columns order.due_date, order.total, client.name.

An INNER JOIN is nothing more than a union, you probably saw this in school, if you didn’t skip math class. It was that class where they talked about Venn Diagrams. Here is a picture to help you out.

Type	Description
(INNER) JOIN	Returns records that have matching values in both tables
LEFT (OUTER) JOIN	Returns all records from the left table, and the combined records from the right table
RIGHT (OUTER) JOIN	Returns all records from the right table, and the combined records from the left table
FULL (OUTER) JOIN	Returns all records when there is a match in the left or right table

From the table above you see that we have several types of JOINS, the one we are using in this example is INNER JOIN, but we could have used any of the others if they solved our problem.

Read the description of each method and try to understand what each one does.

Bottom line

That’s it. I believe that if you want to get started with SQL, these methods and clauses are a good start, no doubt about it. Leave a comment if you missed something, I’ll read them all, I swear.

If you want to practice a little of what you learned, take a look at this link, you can test the queries with a real database, it is really cool.

See you next time!

2 Comments

Aditya Dewan

Very informative article for an SQL beginner - nice!

graliontorile

You made some good points there. I did a search on the subject matter and found most persons will agree with your blog.