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 looking at views.
So the queries that we do here are fairly simple, but queries
can be very, very complex.
I myself have seen queries that were pages and pages long,
just of a single SELECT statement, that's
doing lots of joins and subqueries and various things
so that it makes it very, very complex.
So using a view allows us to encapsulate a SQL query
as a database object.
So views are queries that are stored as database objects.
So what do we use views for?
Well, mainly to reduce query complexity.
So rather than having someone that runs this enormous SQL
statement, this SELECT statement,
on a number of different times, maybe having to type it in,
maybe having to store it in a file somewhere
and then look for it and go get it,
we can actually encapsulate that query--
that SELECT statement-- into a view that's actually
stored in the database.
And then other people can access it, and the user can access it.
The other things that we use views to do
is what's called data hiding.
So let's say that we wanted a certain user to be able to see
certain things in a table--
certain columns, but maybe not other columns.
So maybe it's an employee table, and we
want them to be able to see things
like the first name and last name,
but maybe not their salary.
Then we can use a view to mask that,
and we let the user look at the view and not the table itself.
And it's also important to understand
that views can be updatable in certain situations,
although that's not always advisable,
because it comes with a certain amount of complexity.
So let's go ahead, connect to Scott.
Now, just to be a little different,
it's important, in this situation,
that the Scott user has the permissions
to create a view on his tables.
Just to show you a little bit different way to do it here,
let's log in with SQL*Plus rather than SQL Developer,
as a privileged user to grant Scott that privilege.
So we're going to do sqlplus system
and grant create view to Scott.
All right, so let's begin by looking
at a little more complex query.
This is going to be a join from the emp
table and the dept table.
So let's put this together.
So so far I have the empno, ename, and job columns
from the employee table, and the dname and loc columns
from the dept table.
Aliased as e, dep aliased as d, and join on the deptno column.
All right, so it's joining data from the emp
table and the dept table.
So rather than having to type this query in anytime we need
to do this particular join, we can essentially just
grab the SELECT statement itself and create a view.
So create view, and then the name,
as, and then the SELECT statement.
So remembering what this data looks like,
we'll create the view, and then simply select * from emp_view.
When we execute this statement, it's basically
going to come back to the emp_view and it says,
oh, that's defined as this query.
And it's going to return the same data--
data from emp and dept. We're using a view
to simplify query complexity.
And the other type of view we do is the selective view
for data hiding.
So let's look at the emp table.
So let's say that I want a user to be
able to see ename and job, manager,
and hire date, but not these other columns.
Then I can employ a view for data hiding.
So I'll do create view emp_sel_view as select ename,
job, mgr-- for manager--
and hire date-- so the columns that I do
want the user to be able to see from emp.
And now the user would simply use this query.
And we can see that only those columns that we've specified
in the selective view are available to that user,
and thereby we're accomplishing data hiding.
So those are just two ways that we can use views.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.