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

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.