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

In this lesson, we're
going to be looking

at really the
foundational pieces

of the relational
database model.

And that is the
relational table.

Now, a table is built
to hold relational data

and allow it to be
related to other tables

in the form of
constraints and joins.

Tables are composed
of rows and columns.

So a row is going to be
an individual instance

of information, something
like a customer record,

whereas a column is going
to be an individual type

of information-- so a first name
or a last name or a zip code.

And the tables we're
referring to in this lesson

are known as a heap
organized tables.

They can be distinguished
from a couple

of other types of tables.

But they're called heap
organized because they

store data basically in a heap.

That is to say, as
the data comes in,

that's how it's
stored in a table.

When we create a table, we
have to declare data types

for each of the columns.

So we said that a column was
an individual type of data.

So a first name will
always have character data.

A date will always
have date data.

And a number will always
have numeric data.

So when we define the
columns for our tables,

we use a data type.

And the data types that
we're going to look at

are kind of the basic
ones, although there

are many other types, are CHAR,
VARCHAR2, NUMBER, and DATE.

So CHAR is a
character-based data type

that holds character data.

When we define a CHAR data
type, we use a max number

of characters that we
specify the CHAR---

C-H-A-R-- and then in
parentheses a digit for the max

number of characters.

So if we had a
CHAR(5), we have a five

digit fixed-width character that
can hold those five characters.

So it basically allocates
five characters.

So if we have the word
hello in a CHAR(5),

we see that it fills
all of the five spaces

that are given to
it for a maximum.

If we were to use a state
code like NY in a CHAR(5),

these extra spaces would
actually be essentially wasted

because CHAR(5) will
allocate those out

for the purposes of storage
but will not actually

store anything in them.

So with CHAR, we
make the distinction

of a fixed-width data type.

CHAR is a data type that
Oracle does not recommend

that users use any longer--

that is to say, to not
create tables with CHAR.

It is still allowed
and still works

within the Oracle 12c database.

However, a number of
features that they have

can have problems with it.

So there can be bugs associated
with using the CHAR data type.

Essentially, they no longer
test using CHAR data.

So it's best to go to our next
data type, which is VARCHAR2.

So VARCAR2 is defined
in the same way,

where x in the parentheses is
the max number of characters.

However, VARCHAR2 2 is a 5
digit variable-width data type.

So if we have the word hello
in a VARCHAR2(5), that's

how we would say it, all of
those spaces would be used.

However, if we used the state
code like NY in a VARCHAR2(5),

those extra spaces
would not be allocated.

So there's essentially
a little bit of savings

there in terms of storage when
we use a variable-width data

type.

The NUMBER data type is defined
by two variables, if you will,

that we define for it--

p, s.

p is precision and s is scale.

Precision will be the max
number of digits in the number.

Scale will be the
number of digits

to the right of
the decimal place.

So example, we could
have a NUMBER(5,2).

And this number
would be acceptable.

This number would be acceptable,
even though it's only four

for the precision.

Basically four characters fits
within the five precision.

And this as well.

But this number would not
because even though it

is five digits, essentially
the scale that we define, a 2,

allows for two digits
after the decimal point.

And so if this number was to
be updated, say that column,

and we had two
digits to the right,

we would then have too many
digits, causing an error.

So this number would
not be allowed.

The DATE data type is simply
data containing date and time

information.

And this can be
extracted using the 2CHAR

function when we want to see
the specifics of date and time.

By default, Oracle has
a standard date format

that it shows whenever we
select data from a date column.

There are certain rules
about tables and columns

concerning their naming.

So it's important
to understand those

so we know what goes
into the choosing

a name for a column and a table.

So table and column
names need to be

between 1 and 30 characters--

no more than 30 characters.

For the digits in
the names, we only

allow alphanumeric values and
the hash sign, the underscore,

and the dollar sign.

A table must begin with
a letter between A and Z.

And it's not case
specific, in this case.

It could be lowercase
or uppercase.

Reserved words can't be used
as table or column names.

So a name like select for a
table would not be allowable.

However, most of these
rules can be overridden

using the double quotes.

So if we use double quotes
around a table or a column

name, we can break
some of these rules.

However, I would highly
advise against it.

Because when a
database administrator

looks in the data dictionary for
a table or a particular column,

they're looking
with the expectation

that they're going to find
that in all uppercase.

So if we create a table in lower
case using the SQL statement

CREATE TABLE, Oracle will
store the name as uppercase.

But when we use
the double quotes,

that forces Oracle to store
it exactly as it was defined.

And so we may be looking for a
table with uppercase letters.

And actually they've been
stored as lowercase letters

because someone has created
them using the double quotes.

So the format of the
CREATE TABLE syntax

is going to be CREATE TABLE and
the table name, a parentheses,

and then column,
data type, column,

data type, and so on, then a end
parentheses, and a semicolon.

So let's connect to our system
user and create a simple table.

We have a column1
of CHAR(5) five.

Column2, that's data
type VARCAR2(5).

Column3 will be a NUMBER(5,2).

Column4 will be a DATE.

Execute the statement,
table was created.

Now, if we do a select
star from test table,

we'll see those columns defined.