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

In this lesson, we'll be taking
a look at the Insert statement.

So the Insert statement is
going to be used to put data

into a database table.

And we have a couple of
ways that we can do this.

So to start with, let's
get our connection going.

We're going to use Scott
ORCL, click the plus.

And we're going to go ahead and
click the plus by the tables,

as well.

So here are Scott's tables.

Let's do a select
star from bonus.

And click the Execute
or Run statement.

All right, notice here that
we have no data in this table.

So this makes it a
perfect example for us

to be able to use this to put
the Insert statement to work.

So one more thing
to show us-- when

we insert the data as we put
that data into the table,

we're going to need to know
about the types of data

that are in those columns.

And these are going
to be data types.

So I'm going to use a command
called describe bonus.

All right, so this tells
us about the bonus table.

So we have an ENAME column
that's a VARCHAR2 10 .

And this means it's
a character data

type that's maximum of
10 characters in width.

Then we have a job
that's character

data with a maximum of nine.

And then a SAL--

that's a number-- and a COM--

that's a number.

So when we do our
Insert statement,

we're going to need to
conform to those data types.

So the format of an Insert
statement will be insert into--

table name-- values,
parentheses, and then

the values.

And this is getting us
help here to show us

what those columns are.

So we'll say an ENAME, Fred,
job of the manager, salary.

1,700.

And COM, we'll say 3.

All right, so let's take
a close look at this

before we execute it.

We use the Insert clause
into the bonus table,

then we use the keyword
values to specify what we're

going to put into the table.

ENAME corresponds
to the value Fred.

Notice that this is
a character field.

That's what the
VARCHAR2 tells us.

So that we need to
make sure that this

is character-based data, and is
not larger than 10 characters.

Then we have a comma, and
then another character value,

or a VARCHAR2 value, that's
no more than nine characters

in width.

Manager, and that
corresponds to job.

SAL, we're going to set to
a number value of 1,700,

and then COM to a value of 3.

This is what's known
as positional notation.

So we're inserting
into the table based

on the positions of the values
relative to their position

in the table.

So ENAME is the first column--

this is the first value.

Job is the second
column, manager

is the value that corresponds
to that, so on and so forth.

So if we don't have the
right number of values

that we're passing into
the Insert statement,

we'll get an error.

We have to be careful
with this, and that's

why we need to know
what describe tells us.

It tells us the order of the
columns and their data types.

And so if we were to put
manager here and Fred here

and switch those,
that data would go in,

but it would be incorrect.

So now in order to execute this
statement, we'll click Run.

And it tells us one
row is inserted.

I'm going to come
down here and put

in a second line,
select star from bonus.

And Execute.

And here we are with the
values that we put in.

So that is positional notation.

We also have what we can
call named column notation.

So let's put ENAME, job, SAL,
COM, and then the values.

All right, so we were
going to insert another row

into the table, only we're going
to use name column notation.

And this is where
we can actually

name the columns and then
their corresponding values.

Now notice here that we've
put them in the same order,

but we'll do another
example in a second

where we put them in
a different order.

So we'll execute this.

Again, rows inserted,
select from the table,

and we see the values.

So what would happen if we
shifted some things around?

Well, we need to make sure that
our values here correspond.

So since job is first,
we need to make sure

that we use the value
that corresponds to job.

Next is ENAME,
then COM, then SAL.

Execute, select the data.

And we see that even
though we put the data

in a different
order, listed here,

it went in the proper
order into the table,

since every value corresponds
to the Name column in this list.

So that's what name
column notation does--

it doesn't require us to have
these values in the same order

as they are in the table.