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:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

4 questions on “SQL SELECT

  1. Hello,

    Per you example we are instructed to not put quotes on numbers but I tried this in the online workspace and it worked. I was curious of what type of errors I should of got.

    I am enjoying the site
    Thanks

    • Hello Rocco,

      Very good catch! In fact you do not need to put quotes with columns defined as integer, but if you do, it does not throw any error. It is the same with mySQL and SQL server. On the other side, if you do not put any quote with a column defined as string (for example varchar), it throws an error like ‘Invalid column name ‘xxxx’.’

      I just updated the article thanks to you. I wrote that you do not need to put quotes, instead of do not put quotes.

      I am happy you enjoyed the site. Sorry I could not reply to your question earlier, I had an important family event going on. If there is anything you would like me to enhance on the site, please let me know.

      Kind regards
      Vincent


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