Clauses, operators, and functions in SQL

Clauses
¿What can we do with them?
Depending on what clause we’re going to use, we can do different things, there are five clauses that we can use:

From
We use it to set the table where a field comes from

Where
It is used to set criteria or conditions for rows, it is like a filter for rows

Group By
It can group rows by a field

Having
It is used to set criteria or conditions for a group of rows, it is like a filter for grouped rows.

Order by
It can order rows by a field, by default, all the rows are ordered by the first field.

As you can see in the picture the rows are ordered by the fecha field, by default the clause “order by” will order the rows of text type from a to z, the rows of date type from the oldest year to the youngest year, and the rows of number type from 0 to latest number if we want to change this order we can use the “desc” instruction.

¿When to use it?
After we use a command and when we want to query for more specific information and also there is an order to follow when we use clauses the order is command + from + where + group by + having + order by, there is no need to use all the clauses it depends in what we want to query

Operators
After using clauses we can use operators, operators are used to set a more specific query.
There are two types of operators, the logical operators, and the comparison operators.

Logical Operators

And
It can concatenate criterias

Or
It can concatenate instructions with the same criteria

Not
Not logic

Comparison Operators
Greater than
'>
Less than
<
Greater than or equal to
≥
Less than or equal to
≤
Equal to
=
Not equal to
<>

Between
It is used to set a range of conditions

As you can see if we want to use “between ”also we have to use the “and” operator.

Like
Used with wildcard characters

In
Used to specifying rows in a determinate field.

Grouped queries or total queries
¿What is a grouped query?
It is a query where we get a total result of rows that are grouped.

¿When to do a grouped query?
When we want a total result of rows that are grouped like for example… A query where we need to know the total of persons of a field or the max value of a field of values.

¿How to do a grouped query?
We need functions and fields, we need two fields, one field is going to be the field where rows are going to be grouped and the other field is going to be the field where we’re going to get the totals, so a field for rows and other for totals.

Alias and names inside a query
What is an alias?
It is a way in how we can change the name of a column in a query, I said query because just we are changing the name inside a query if we change the name of a field inside a query that doesn’t affect the original column’s name if we have an alias inside a query we need to use the alias and not the original name.

Aggregation functions
We have functions called aggregation functions that are used to make grouped queries, aggregation functions are:

AVG
We used it to get the average of a field.

Count
We used it to count the number of rows in a field.

Sum
We used it to sum the rows of a field.

Max
We used it to get the max of rows of a field.

Min
We used it to get the min of rows of a field.

Extra important information
Where clause select rows to display before they been grouped for an aggregation function while having clause select rows to display after they been grouped for an aggregation function.

When we use the aggregation functions we use the having clause, also we can use the where clause but there is no logic in use it, because “where” is used for setting criteria for rows that are not inside a group, instead of using “where” in a query where we have aggregation functions, we can use “having” that is used for setting criteria for rows that are inside a group.