Blog

Welcome to the ultimate tutorial for beginners aimed at learn how to create SQL SELECT queries. In this tutorial I am going to transmit to you, in a simple and direct way, what are the most important parts of a SELECT query in SQLWhat are they for and how are they used?

you will also find several simple SELECT SQL examples related to the different points of the article. Among other examples, you will find, simple record retrieval query examples in SQL and sample queries sql with basic data filtering.

Content

• What is a SQL query?
• What are the parts of a SELECT SQL query?
  • Minimum parts of a SQL query
  • Optional parts of basic SQL SELECT queries
• Simple examples of SQL queries with WHERE
• sql queries with multiple conditions in WHERE
  • Simple examples of queries with multiple conditions
• SQL ORDER BY
  • ORDER BY Example
  • Observations on ORDER BY

What is a SQL query?

A SELECT SQL query is a question to a database using the SELECT statement belonging to the SQL language. The query extracts information from the database in a format that is readable by the user of the database.

For example, if you have a table with the information of the employees of a company, you can run an SQL query that retrieves the information of the highest paid employees. This query to retrieve useful information from employees is a typical query that you would normally perform on a database.

The information retrieved by a query is always made up of records (also called tuples) that could be defined as the “rows” of the tables, since the “columns” are the independent fields of each row or record.

What are the parts of a SELECT SQL query?

An SQL query is made up of several parts.Some are optional and some are mandatory.

The optional parts are 2:

1. select
2. DESDE

And the optional parts of the SQL queries are:

• WHERE
• GROUP BY
• ORDER BY
• LIMIT
• OFFSET

Minimum parts of a SQL query

How mandatory we find 2 parts for any SQL query to be executed:

1. SELECT, this sentence is in charge of configuring which fields (columns) or data we are going to recover and how it has to be called. For example we could make a select that retrieves the first and last name: SELECT first name, last name.
2. FROM, this statement tells the database where The information is going to be retrieved, that is, to which tables the query refers. Continuing with the example of the previous point, we could indicate that we want to retrieve the columns name and surnames of the table employees: SELECT first name, last name FROM employees.

Below you can see several examples so that you finish understanding these concepts:

1. Query that retrieves the make, year of manufacture, model, and price of all cars in a database:

SELECT marca, anyo_fabricacion, modelo, precio
FROM coches;

2. Query that retrieves the information of teachers from a teachers table:

SELECT nombre, apellidos, dni, direccion, telefono, titulacion
FROM profesores;

3. SQL query that retrieves all student columns from a student table:

SELECT *
FROM alumnos;

Optional parts of basic SQL SELECT queries

How in this tutorial I just want to explain simple queries, I am not going to explain all the optional parts that exist. However, I am going to focus on explaining the statement that adds filtering to the records that we want to retrieve. This part of the query is the WHERE, what, as its name indicates in English, refers to where, but not a “where” of place, but of CONDITION.

Look at the following example for better understand this WHERE sql statement:

SELECT nombre, apellidos
FROM trabajadores
WHERE salario > 3000;

This query would read as follows: Select the first and last names FROM the workers table WHERE their salary is greater than 3000.

how did you see this where refers to the condition of the query. If you know how to program, it would be like an IF that is checked before retrieving the information from the SELECT.

Simple examples of SQL queries with WHERE

Next, and to finish this tutorial aimed at a beginner level, I leave you a series of simple SQL query examples with conditions on WHERE.

1. SQL query that retrieves the students who were born after the year 90:

SELECT *
FROM alumnos
WHERE fecha_nacimiento > '1989-12-31';

2. SELECT query in SQL to retrieve with WHERE only drivers who have 12 points on the driver’s license:

SELECT nombre, apellidos, dni, fecha_nacimiento AS nacimiento, puntos 
FROM conductores
WHERE puntos = 12;

3. Query (query) in SQL to select all students who do not have siblings:

SELECT *
FROM alumnos
WHERE hermanos = 0;

sql queries with multiple conditions in WHERE

To finish this basic tutorial we are going to see the multiple conditions, which serve to add a bit of complexity to our SQL queries.

In general, we can say that there are two ways to add conditions to an SQL query:

• AND (intersection of two conditions): this sentence allows you to chain two conditions in such a way that the condition a AND b must be fulfilled. For example, we could perform a query that selects all students under 18 years of age and who do not have siblings:

SELECT nombre, apellidos, dni
FROM alumnos
WHERE edad < 18 AND hermanos = 1;

• OR (union): with this statement we can join two conditions that will evaluate to true if one of the two is true. In this case we could select all minors or retired people:

SELECT nombre, apellidos, dirección
FROM personas
WHERE edad < 18 OR edad > = 65;

You should know that we can use as many conditions as we wantyes, so only one WHERE clause is used per query.

Now we are going to see several examples joining several conditions. For each case I will leave you a brief explanation of what the results would be if you executed them in a suitable database.

Simple examples of queries with multiple conditions

1. Query that retrieves the records of a table people for those who are under 18 and working or those who are retirees who are working. For more clarity i will use parentheseswhich is how I recommend you compose multi-condition queries.

SELECT *
FROM personas
WHERE ( edad < 18 AND trabajando = 1 ) OR  ( edad >= 65 AND trabajando = 1 );

//forma alternativa de realizar la misma consulta

SELECT *
FROM personas
WHERE trabajando = 1 AND ( edad < 18 OR edad >= 65 );

2. Simple query with several filters in the WHERE that recovers the players basketball players who are taller than 2 meters and are pivot or the players who are eaves.

SELECT nombre, dorsal, equipo
FROM jugadores
WHERE ( altura > 200 AND posicion = 'PIVOT' ) OR posicion = 'ALERO';

3. In this last example, I show you a SQL query with two conditions that retrieves the records of the users of a website, who visited it between a period of 7 days and who use the browser chrome or those that your IP starts with 127.

SELECT ip, ultima_visita
FROM usuarios
WHERE ip LIKE '127%' OR ( navegador = 'chrome' AND ultima_visita BETWEEN '12-12-2017' AND '19-12-2017' );

SQL ORDER BY

After successfully teaching you the WHERE clause you should now learn how to sort the records returned by SELECT SQL queries.

For ordering there is the SQL ORDER BY clause. This clause allows, in an extremely simple way, to order the returned records according to the values ​​of their columns.

ORDER BY Example

In the following example I am going to order the rows returned by the SELECT by the name column:

SELECT nombre, apellidos, dni
FROM clientes
ORDER BY nombre;

This query will return the customer rows sorted by their name column. By default, the value of the chosen column will always be ordered ASCENDING.

In this case, also, since it is a text type column, it will be in alphabetical order.

In a query with ORDER BY we can say if we want the ascending or descending order, for this we will use the reserved word ASC or DESC. Look at the following example for clarity:

SELECT nombre, apellidos, dni
FROM clientes
ORDER BY nombre DESC;

Now I have indicated that I want the descending order with “DESC”. So it will be a reverse alphabetical order (from Z to A).

We can also indicate several ordering columns. What for? Well, if the data in the column to be sorted coincides, choose this other sort criteria.

For example now I am going to make sure an order with 2 columns:

SELECT nombre, apellidos, dni
FROM clientes
ORDER BY nombre ASC, apellidos ASC;

with the latter SELECT with GROUP BY I’ve told it to sort the results alphabetically by their last names when the first name matches. The result of a query like this could be:

If you notice, when the name matches the order is done by the last name.

Observations on ORDER BY

1. The order by clause It will always be written after the WHERE, if there is no WHERE we will do it after the FROM
2. Can specify one or more columns as sort order. By default, an ascending order will be performed.
3. You can indicate the type of ordering: ascending with ASC or descending with DESC.
4. The columns indicated in the ORDER BY clause will be ordered according to their TYPE. In other words, those of the text type will be ordered alphabetically or those of the numeric type in numerical order. Dates and times are also detected.