All language subtitles for [SubtitleTools.com] Select - Learning Oracle 12c [Video]

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.