If you already know how to create tables with SQL and insert rows into SQL, the next step on the path to mastering SQL is learn how to query stored rows.
The SQL queries for data extraction They are by far the most varied and allow for the greatest complexity of existing SQL statements for working with tables (SELECT, INSERT, UPDATE, or DELETE).
In this tutorial I am going to explain you with a point of view aimed at beginners how to use SQL SELECT query.
The different sections of this tutorial are:
- Which is the SQL statement to query records in databases?
- What is the SELECT SQL query for?.
- structure and examples of basic usage of a SELECT query:
- SELECT Examples with WHERE in SQL.
- SQL statement to query records in databases
- What is the use of the SELECT query in SQL?
- SELECT SQL syntax and examples
- The columns of a SELECT SQL
- FROM clause of SELECT query
- The conditions of a SELECT
- SQL Query Advanced Concepts
- Aliases of the columns of a SELECT
- SQL functions
- end of basics
SQL statement to query records in databases
Just as to insert data into tables we use the INSERT statement or to create a table we will use a CREATE SQL, to retrieve the rows stored in one or more tables we will always use a SELECT statement.
The SELECT statement only has the purpose of obtaining the rows stored in databases and it will be, as a general rule, the query par excellence when we work with relational databases.
What is the use of the SELECT query in SQL?
Running SELECT queries of greater or lesser complexity, you can perform any type of data extraction you can imagine. There are configuration parameters that allow us to indicate a multitude of conditions to obtain only the records that are needed for each case.
As the basis of any dynamic web application is the information stored in a database, all content that you enjoy today through a web is carefully extracted with a specific SELECT. So you can imagine how powerful and complete this query is.
A real example of the use of SELECT statements would be to display the news from any blog, be it a WordPress, Joomla or any other type of development blog.
SELECT SQL syntax and examples
The structure of a SELECT query, Like all other SQL programming, it follows a clear and easy-to-understand logic. Thus, the elements that intervene in a general way in a SELECT query are:
- The fields (columns) that we are interested in retrieving from each queried record.
- The table we want to read from the registers.
- filter conditionsso that we only get the rows of the table that meet certain requirements from the query.
These 3 items seen are what you need to think about before you start creating any SQL SELECT query.
Each data to be configured in the query is placed in a part of it following the following structure:
SELECT columnas FROM tabla [WHERE condiciones]
Explanation of the parts:
- Columns are written as is their name in the database.
- The table must correspond to the columns.
- Conditions are optional (That’s why I’ve put them in square brackets.)
So that you become familiar with how the SELECT queries are going to be, I leave you a simple example:
SELECT nombre, apellidos FROM jugador WHERE edad = 21;
The columns of a SELECT SQL
when you perform a SELECT query You must always specify the columns to get, that is, the columns read for each record retrieved. Among the options available to you are: indicate that you want to read all the columns, one or more.
Options to indicate columns:
- If you want to retrieve all the available columns you can indicate the wildcard character “*”.
- If you want to retrieve more than one column, you will indicate the names separated by commas.
- You can modify the name of a column with an “alias”
Example 1 SELECT from all columns:
SELECT * FROM jugador;
Second 3-column SELECT SQL example
SELECT nombre, apellidos, partidas_jugadas FROM jugador;
FROM clause of SELECT query
The FROM clause of an SQL query determines the origin of the selected columns. Therefore, we must take into account which tables we want to read the query information from.
Thus, if for example we wanted to read all the columns of a table called players we would write the SELECT like this:
SELECT * FROM jugadores;
Although this tutorial is for basic use, you should also know that more than one table can be indicated as source in the FROM clause:
SELECT * FROM jugadores, equipos;
The conditions of a SELECT
In order to filter the results to be read, we will use certain conditions, so that the rows obtained must comply with them. This is accomplished with the WHERE clause.
This clause allows SQL to define all the conditions we want until the desired complexity is achieved.
Let’s see several examples.
Example of SELECT for players of legal age:
SELECT * FROM jugadores WHERE edad > 17;
SELECT SQL for players named “John”:
SELECT nombre, apellidos, edad FROM jugadores WHERE nombre = ‘Juan’;
SELECT SQL to get the records that have the last name ‘Rodríguez’ and are of legal age:
SELECT nombre, apellidos FROM jugadores WHERE apellido = ‘Rodríguez’ AND edad >= 18;
NOTE: In this example I have used the AND operator to specify that both conditions must be true at the same time.
Query to get the rows of players who have the last name ‘Rodríguez’ or ‘Gómez’:
SELECT nombre, apellidos FROM jugadores WHERE apellido = ‘Rodríguez’ OR apellido = ‘Gómez’;
SQL Query Advanced Concepts
To finish, I am going to give you an introduction to certain slightly more advanced concepts of queriesalthough without going into really complex SQL stuff.
Specifically, we are going to see certain options when configuring the columns of a SELECT:
- He column aliases.
- Simple SQL functions to select other data types.
Aliases of the columns of a SELECT
When we continually work with SQL, there are times when we need to manipulate the results obtained from our queries. In the case of SELECTs, it is very common to have to change the name of the column to make it readable for display to a non-technical user. This is done using a alias for the column name:
SELECT edad AS ‘EDAD RECOMENDADA’ FROM pelicula;
For example, it could be the case that we are asked to obtain a list of all the clients of a company (stored, of course, in its relational database). And the columns in this table could be abbreviated or different names than what you expect, for example a commercial of a store.
Imagine then that we have created a table with the following CREATE query:
CREATE TABLE cliente( id INT PRIMARY KEY, nom VARCHAR(50), ape VARCHAR(100), dir VARCHAR(200) );
Now we are in the previous case, and we want to get a report or CSV with the data of certain clients, specifically the records of the clients that are from Madrid, and we want the results to be displayed in a readable way in the titles:
SELECT nom AS Nombre, ape AS Apellidos, dir AS ‘Dirección’ FROM cliente WHERE direccion = ‘Madrid’;
There is a huge list of SQL functions, but in this short introduction, to give you an idea, we are only going to deal with 2: COUNT and CONCAT.
SQL SELECT COUNT function
With the function COUNT(column) we can obtain in a column the count of the different values for the specified column.
SELECT COUNT(id) FROM clientes;
If 200 customers were stored in the table, the result of this query would be a single row with the value 200.
CONCAT SQL function
This other native function in SQL It allows us to join the value of the columns that we want, so that the final result is its set.
An example Worth more than thousand words:
SELECT CONCAT( nom, ape) as ‘Nombre Completo’ FROM cliente;
end of basics
If you have come this far, you already know how to consult information stored in the database. With the knowledge of this tutorial you will be able to create simple SELECT queries.
Now it’s your turn to practice and practice. With a bit of experience you can move on to the advanced query concepts that we’ll cover in upcoming SQL articles:
- More complex operators for the conditions of a WHERE.
- Sort the results of a query.
- Group the obtained rows.
- Relate tables to each other to obtain information in a more complex way.
- Nest queries.
I hope this tutorial has been useful to you! If so, I would appreciate it if you shared it, it will help us and encourage us to create new quality content like this article.