A select statement is a sentence written in SQL. The purpose of this sentence is to retrieve information from a table.
The simplest select statement in the world
Let us retrieve all rows from a table named “clients”. For each row, we want to display the values of all columns:
In this statement:
- the symbol * means that we want to display all columns.
- “clients” is the name of the table.
A little less simple: display specific columns
Now let us retrieve the same information, but showing only three columns: first_name, last_name, and phone_number. Here is the corresponding SQL select statement:
In this example, first_name, last_name and phone_number are the names of the columns to display.
More powerful: add a condition to your select statement
We just learnt to display information about all rows in a table. Now we will learn how to display only certain rows in this table. To do that, we need to specify a condition that rows should fulfill to be displayed.
If we want to display only rows for which the column “state” is equal to “karnataka”, we just add the following text at the end of our select statement:
where state= 'karnataka'
You can use also double quotes (“), which is equivalent:
where state= "karnataka"
The final select statement looks like this:
In this statement:
- state is the name of a column in the clients table.
- only rows for which the column “state” is equal to ‘karnataka’ will be displayed.
Why is Karnataka between quotes?
In the table clients:
- values in the column region contain text (combination of letters and numbers). In this case, we put the value between quotes (simple or double quotes).
- values in the column phone_number contain only numbers. In this case, we do not need to put the value between quotes.
If we use a column containing only numbers in the condition, the select statement looks like this:
In the condition, can we use one of the columns that we want to display?
Yes, you can use any columns after “select”, and any column after “where”, as long as they belong to the table you indicate after “from”.
select first_name, last_name, phone_number
I want to display revenues in another currency than the one saved in the table.
You can display a value equal to the value found in a column modified by a mathematical function. Note that this will not change the value of the column inside the table. Example of mathematical functions:
- Multiplication: *
- Addition: +
- Subtraction: -
You can use one of these mathematical functions to display the results in another currency.
Example: revenues are written in dollars in your table and you want to display them in euros. In this example we assume that 1 dollar = 0.91 euro (you may have to write 0,91 if you are in Europe):
In our example, here is the table sales:
If you use the following query:
select ID,revenue*0.91 from sales
You will get then get the following results:
This displays the revenues in euros. The table will still contain revenues in dollars.