SQL SELECT

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:

select * from clients

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:

select first_name, last_name, phone_number
from clients

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:

select first_name, last_name, phone_number
from clients
where state='karnataka'

or

select first_name, last_name, phone_number
from clients
where state="karnataka"

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:

select first_name, last_name, phone_number
from clients
where phone_number= 4988788776677

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”.

Example:
select first_name, last_name, phone_number
from clients
where first_name=”arun”

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: *
  • Division:/
  • 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:

IDrevenue
112
215
325

If you use the following query:
select ID,revenue*0.91 from sales

You will get then get the following results:

IDrevenue
110.92
213.65
322.75

This displays the revenues in euros. The table will still contain revenues in dollars.
 

To test your understanding of this lesson, take the SQL Select Statement Quiz or practice select with a real database. You can also go to the next lesson about SQL DISTINCT, ORDER BY, COUNT.
 
Share on:


Author:
Last udpated: December 11, 2017
Created: April 8, 2014