Mastering SQL: Essential Commands for Beginners

SQL SELECT

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

This command is used to select data from a database. See the example below.

SELECT column1, column2, ...
 FROM table_name;

We can translate this as: “Select columns ‘column1’ and ‘column2’ from table ‘table_name’;

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

SELECT * FROM table_name;

Once you run this command, the returned data is stored in a results table, called a result set.

I think it’s best to demonstrate this with an example. Let’s create a table to practice.

The table we will use will be this one. “Clients” table

Table: customers

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

SELECT name, city FROM customers;

And your result set should look something like this:

Result set

SQL WHERE

As we just saw, with SELECT you can filter 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 this we need WHERE.

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

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

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

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

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

Result set

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

However, numeric fields should not be enclosed in quotation marks:

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

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

SQL LIKE

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

I know, better with an example, right?

Let’s say we need to update customers’ addresses in our database.

We need to change every Customer whose address has the abbreviation “Av” to “Avenida”, 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 cannot use just WHERE.

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

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

Result set

You might be wondering what the hell that “%” is doing there, or that I made a typing mistake, but no, everything is fine.

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

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

There are many possible combinations, but I will list the ones I consider the most important.

LIKE Operator

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

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

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

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

We can still upgrade this query, instead of executing two, we can execute just one query that will solve our problems. Our next operators are here for that.

AND, OR and NOT operators

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;

The table below provides a brief description of what each operator does.

AND, OR and NOT operators

So, as I said before, we can simplify the query from 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 customers
 WHERE (address LIKE  'Av. Brazil 222'  or address LIKE  'Av. Paulista 321' );

Result set

SQL UPDATE

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

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

Well, the first step is to select the Client who is moving, right? And we already know how to do that, using SELECT and WHERE.

But now we’ll use the UPDATE method, to do exactly the same thing, with an extra step , we’ll update the values ​​at the same time. Like this:

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

Table: customers

Here we have the UPDATE command instead of SELECT. And right after we have the line with the command (or keyword) SET, which 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 ‘clientes’ table the ‘city’, ‘country’ and ‘address’ of the client whose id is equal to 1”.

Simple, right? We just sent Rodrigo to France.

Now, pay attention!

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

SQL DELETE

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

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

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

This is your cue to use DELETE.

In the example, this would look like this:

DELETE  FROM customers WHERE name= 'Clovis Oliveira' ;

Table: customers

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

But be careful!

If you do not provide a WHERE in the clause with DELETE, the entire table’s data will be deleted!

!!! THIS WILL DELETE ALL DATA FROM THIS TABLE !!!
 DELETE  from customers;

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