The SELECT Statement in its Simplest Form

In this lesson, you will start learning about the SELECT statement, but this is just the beginning because we will be working with this command for most of the lectures in this course.

So, what is the SELECT statement?

The SELECT statement is the SQL command that is used to retrieve or read information from a database, and it can be very simple or quite complex depending on the features you decide or need to use.

A SELECT command is usually what is called “a query” and that is a very common way to call it, but in reality, other commands  that are used to modify or delete information from tables have also a query component, which is called an implicit query, because internally they need to retrieve the rows they are going to modify or delete.

In its simplest form, in a SELECT command, you specify the columns or values you want to retrieve, and the table where those values are stored.

So, you might remember that I talked about a products table in the lesson about basic database concepts.  I had 5 products in that table, which included aspirin, penicillin, and others.

So, if I want to get the list of products that I have stored in my database, I have to tell it what information I want to get, for example, the name, or the price of the product, and I have to tell it what is the table where that information is stored, which in this case is my products table.

So, my request to the database must be specific, and I have to tell it something like this:

GIVE ME THE NAME AND PRICE OF ALL THE ROWS STORED IN MY PRODUCTS TABLE.

But Oracle is not going to understand me if I use that language.  I need to tell it what I want in the language he (or she) understands, which is SQL.

So, translated to SQL, this is my request for the database:

SELECT product_name, price
FROM products;

I have to use the word SELECT to tell it the columns I want, which in this case are product_name and price, and I have to use the word FROM to tell it the table where the data I want is stored, which in this case is products.

Clauses

Let’s take a closer look at this command.

The 2 words in uppercase, which are at the beginning of each line in the above command, are keywords or reserved words.  What that means is that they are part of the SQL language.

Product_name and price are columns of a table, which in this case is “products”.

So, this command has 2 parts:  The first one is what I wrote in the first line, and the rest is the second one.

In SQL, these parts are called clauses.

So, in the first line, we have the SELECT clause, which includes the SELECT word, and the list of the columns I want to retrieve.   And in the second line, we have the FROM clause, which includes the reserved word FROM, and the name of my table, which in this case is products.

This part of the SELECT clause after the keyword is what is called the “select list”.

The Results

Okay, If I run this command, this is the result:

This list of rows that I get from the database is called a “result set”, and as you see, it has rows and columns and is very similar to the structure of a table.

Now, if I wanted all of the columns from this table, instead of listing every column in the SELECT clause, I can put an asterisk, or a star, as some people including myself like call it.

So, the most simple form of the SELECT statement ever is the one where I ask for all of the columns and all of the rows of a table, which looks like this:

SELECT * from products;

The SELECT statement can have many other clauses, but these 2 are the only ones that are mandatory.  In other words, these 2 clauses are the only ones that must appear in every select statement.  If one of them is not present, you will get an error.

And a very important point to make here, is that I can write the whole statement in a single line, or I can separate it in more than one as I did in my first example, and it will work the same.

Also, I can write any part of it in uppercase or lowercase, and it will also work.

So, the decision to write your commands in one way or the other should mostly be based in legibility and the use of common practices, and if you are going to be doing this as part of your job, in the company’s coding standards.

One common practice is to write the reserved words in uppercase and to put every clause on its own line, but it is not a rule, but just a common convention.

So, now I need you to write some SELECT commands, so that you start feeling the joy of giving instructions to your database in its own language.

Your Turn

So, go ahead and run SQL Developer, and write and run the SELECT commands necessary to accomplish the following tasks:

1. Get the list of names, phones, and salaries of all employees.

2. Retrieve all of the information available in the department table.

I will not provide an expected output this time, because these are really simple tasks in which the only goal is to make you test and write commands that actually run without errors, and get a result from the database.

Stop here, and continue when you have completed your task.

…

Hey, Welcome back!

How did you do?

How did you feel?

Was it nice?  Was it is easy?

That’s great because this is just the beginning!

What can be included in the SELECT list?

Okay, to finish this lesson, you need to know that besides listing columns in the SELECT clause, you can also include literal values, or constants, such as numbers, or strings.

You can also include expressions, function calls, and pseudo columns.

Look at this example:

SELECT name, 25, 'test', salary/50, to_char(hire_date), rownum
FROM employee;

Here, 25 and ‘test’ are literals, and as you see, string or text literals must be enclosed in single quotes.

TO_CHAR is a function.

I’m not going to go into details about what functions are by now, because they are covered in detail in a future lesson.  By now, suffice it to say that a function is a small program that returns a value, which may or may not be based on a value or list of values you pass as parameters.

In this case, we are calling the to_char function passing hire_date as a parameter, and this function is executed for every row in the result set of our query.

And a pseudo column is a phantom or “fake” column that doesn’t really exist in any table but is a value that is available during the execution of a query, and can be useful in some situations.  For example, in this case, the rownum pseudo column is a number that is assigned to every row that is returned by the query.

Your Second Task of this lesson

Now, before finishing this lesson, please stop reading again, and write a query to get this information:

The company is going to give all employees a 20% increase on their salaries, and your task is to display a list of all of the employees which includes the employee id, the name, the current salary, and the salary they will have after the 20% increase.

Expected Output:

 Please don’t continue to the next lesson if you have not completed this task.

P.S.  Remember that the scripts you need to run (if you haven’t) to create the tables needed for the practice tasks can be downloaded from the links at the top of this page.