Original subtitles

In this lesson, we're going to take a look at what is probably

the most important statement in the SQL programming language,

and that is select.

So let's start SQL Developer.

That was our tip of the day.

And we're going to connect to the ORCL Database

using the scott user--

using these connections we've set up.

I'm just going to click the plus next to scott_orcl,

and now we have our worksheet, here, to type in SQL commands

and see the result. I also wanted to point out, here,

in SQL Developer that if we click the plus besides Tables,

we'll get a list of all of the tables

that are owned by the scott user.

So our connection is using the scott user

to connect into the database, and these are the tables

that Scott owns-- the scott user.

So when we use a select statement,

we have a certain format that we will use.

Let's start with the basic format

and go more advanced as we go along.

So let's do a select * from emp.

EMP is one of the tables that we have here,

and we're going to type in a select * from EMP.

Notice that we end with a semicolon.

So the semicolon is what we call a statement terminator in SQL.

Also notice that whitespace is ignored in the SQL programming

language.

So it doesn't matter if we have this on multiple lines or one

line, or even a line, almost, for every statement or clause.

Either way, it'll execute the same way.

So whitespace is ignored, and that's

what the importance of that statement terminator

is, because it's free to ignore whitespace

because it knows it has to read the command until the statement

terminator.

When we want to execute a statement in SQL Developer,

we click the green arrow, or Run Statement.

Let's look at what we have here.

When we executed select * from the EMP table,

it's showing us all of the columns and all of the rows

in the EMP table.

So we have columns like EMPNO and ENAME

and JOB, and then all of the different rows.

Notice that this is the table itself, or the table data.

This column here is actually provided by SQL Developer just

to show line numbers.

So you see there's no column name here.

So we always keep that in mind, but those are just line

numbers, and they're not data.

So select * from EMP gives us this data.

So the format of a select statement

will be select, and then either a * or column name,

and then a from.

So the simplest form of select statement has select and from.

But what if we don't need all of the columns,

we only want one column?

Then we change this to select ename from emp.

These commands that pop up here are just helper commands

from SQL Developer.

We can ignore those.

And we click our green arrow again,

and it presents us with this column-- the ENAME column--

and no other data besides that.

We can also do multiple columns--

ENAME and JOB.

We get the employee and the job.

These correspond as if we had brought all the data back.

The ENAME SMITH goes with CLERK.

ENAME ALLEN goes with SALESMEN, so on and so forth.

Let's go back to *.

Now, we just did what's called column restriction.

So we restricted the data we brought back based on column.

We can also restrict data based on rows.

And to do that, we need a condition.

Here we use a where clause.

So we'll say where ENAME = single quotes--

let's change this to job.

It will show us more data.

job = 'SALESMAN.'

And execute.

So this gives us the data on all the salespeople

that we have in this table.

Now, notice that we have single quotes around salesman,

and within single quotes, case and whitespace are preserved.

So even though we can type our statements

either uppercase or lowercase, in the statement itself,

if we're in single quotes, then we have to respect the case.

So since it is uppercase in the table,

we put uppercase in the statement as well.

So here we're using a condition to restrict the rows that

come back in the query.

And we can also do this.

And that is the nonequivalence.

So this is a nonequivalence query.

So we're saying select * from the EMP

table, all the columns from the EMP table

where the job is not salesman.

And execute, and so all of the people and employee information

in the table come back except those

that have the job salesman.

You can see there's no salesman here in the rows.

So select is a clause, from is a clause, and where is a clause.

Now, where is optional, and we're

going to look at another optional clause--

order by.

So the order by clause is going to be used to sort data.

So let's say that we want to see this data in a certain order.

So let's decide-- what should we order this by?

It looks like [INAUDIBLE] is already ordered.

Let's order by their date that they were hired.

So we say order by hire date.

Always add the semicolon at the end.

And click Run Statement.

So again, we're doing select *, so we're

getting all the columns, all of the rows,

but we're just ordering them by the date that they were hired.

So this is how we get intelligent information

from a database.

We use this massive information to answer questions.

So let's extend this one more time.

Let's put a where clause in here where job = 'SALESMAN,"

order by hire date.

So here we're using the select clause, the from, the where,

and the order by clause.

We execute, and we have all of our information

on our sales people, starting at the oldest hire date

to the most recent hire date.