Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.