Hi. In today's lesson, we understand what is a view? How do we create it? How do we use it? What are the advantages of creating a view? To start with?

A view is a window through which you can access a portion of one or more tables. You can access data that is present in a table through a view. You might be wondering, when we have the data in the tables, why to create a view to access the same data? Well, there are a couple of very strong reasons to do it. But before that, a view itself does not contain any data. That's the most important point.

We are not creating a copy of the data data that is already present in the table. Instead, we are creating a view on the top of the table to access a part of the table so that we can hide confidential data and make only the data that is needed by others available to them. That is one of the reasons why we create view, not the only reason. But let us get into more details. Here is my department's table. But I want others to access only two columns out of four columns.

So I can create a view and I can make this view available to others. How do we make a view available to others so that they can access view but not the underlying table will be our discussion later in the course in a lesson called security But for now, let's understand how to create a view, how to use it. But the goal is to make these views available to others so that we can hide confidential data and make only relevant data available to others. So in this example, we are creating a view called DPT, which takes only two columns from department's table. This is how a view looks like. A view is internally not storing any data, but storing only the query, use it to create the view.

So when you access a view, you're actually accessing a query. So a view is also known as stored query. Because all that it's stored in the database is just a query and that query is internal accessing the data present in one or more tables, this is about the view. And these are the advantages. The first and the most important advantage, restricting access to data in the table. We don't want others to access all the rows and columns.

So we create a view on the top of the table to specify what part of the table the view should access. And when we let others access view, they get access only to a part of the table. This is also known as discretionary access control. It simply put, you allow others to access only what they need to access nothing more. Apart from that, views are also used to make complex queries easy By creating a view with a very complex query, we don't have to type that complex query every time. Instead, we just refer to view.

And also, views can be used to present a data which is not there in the table. It can split a single column into multiple columns. For example, I'm interested only in the year of employee joining the company, not the whole date. I can extract each year from hire date and make it available as a separate column through view. I can also put first name and last name of employ together and call it as the full name in the view. So your view can present the same data in different ways.

And finally, views can also reduce data dependency Makes your applications more independent of data. How are we going to achieve it? We'll see later in this lesson. For now, let's create a view. This is how we create a view, create view is the command and you give the query that is to be used to retrieve the data. And that is all that you store in the database.

At the time of creating the view, if you already have the view, you can replace it by using our replace option. So when you say create or replace, if the view is not there, it is created. If the view is already there, it is replaced. We will talk about with check option little later. And this is about the last feature, the data independence. How do we make Programs data independent, more specifically put structure independent.

In my first scenario, I have two programs, Java program PL SQL program, accessing the data in table. The table is supposed to have five columns. The program's depend on those columns. But for strong reasons, I'm splitting the table into two parts. Now table one contains first three columns, table two contains the remaining two. And there is a link between table one and table two through C one.

But now the problem is, my Java program cannot access column C four and C five in that table. So the query is to be rewritten, so that it has to access Tables instead of just one table. So is the case with PL SQL program. So is the case with every program that depended on table. Now, the problem is maintenance whenever there is a structural change in the table, you don't want to go back and modify your programs written in different languages. So, the best way to handle this is this, create a view.

The view is internally talking to the table. The view is what our programs would access. They don't know anything about table, they just talk to view, the view is internally talking to table in this particular scenario, it might seem unnecessary and redundant. But as we discussed what if the table is restructured now the view comes to you To rescue, the view is recreated, so that it takes data from two tables, not one. And programs continue to access the same view. As the result, there is no change in the programs, but the view was recreated.

So creating a view is going to make your programs independent of the structure of table. And this is a big advantage because typically you have lots of programs, depending on the tables. So instead of programs depending directly on the table, it's better we have a layer on the top and let your programs talk to that layer. This is how data independence can be accomplished in the context of your applications. Now, let us see how to create some views. Well the terminology is simple.

The table on which the view is based is called base table. So, in this example, the base table is employee's table. The name of the view is it employees, because I just want to take only implies doing the job where the job ID starts with it. So, all it implies are access to using it implies view. But do not forget there is no duplication of data. We are only storing the query and accessing the data by using this query.

We are not duplicating the data that is a very important point related to views. The second example is to create an another view, which is providing the data that is not present in In the table like full name, this is where I want to get the full name of the employee by concatenating last name and first name. And this is where I want to calculate the total salary. So when I say get the data from EMP salary, the name of the view, I'm going to see all the details and these are not available as they are in the table. So, let us jump to our SQL Developer. And let us create the first view this is how we create the view.

Let's go run it and the view it implies created. And now if I say select star from it implies it is going to get me only it employees and nobody else. So these are the it implies and we got a few roles and Few columns, because that's exactly what the view is expected to provide. In fact, the user who is using this does not even know this is a view, because it looks just like a table. For example, I can even use where class and many other classes on this. For example, I can say, where salary is greater than 5000, then we will bring only a few rows from the view.

So everything that we do on the view is internally going to the base table. Now this is my second view, where I'm trying to create some information that is not available straight away in the base table. So let's create the view. And now if I tried to retrieve the data saying Select star from EMP salary, This is going to get me the details like this. So for every employee, we get the full name and the total salary, especially for those where we have commission, it is adding commission to the salary. And this is the calculation salary plus the commission.

Of course, we are using NVL. To ensure for those roles where commission percentage is null, we don't get null as the total salary. We have discussed about this problem, and the solution in the previous lesson. So this is how we can create a view and make use of it. And here are these aliases in the context of a view you must give alias if you don't give alias This is not a valid column name. So the creation phase, so you have to be aware of These requirements.

So once the view is created, we are ready to access that. And in this case, we are presenting data which is not available in the base table, like the total salary and full name. Also try creating a view, which is only displaying the name of the employee and the year in which implied joined, then you need to extract only ear from hire date. Now, as you can also use any complex query, I'm creating a view called a job summary where I'm going to get job ID and then the total salary for all the employees doing the job. Let's create this view. And in this case, when I say select star from job summary, it's just going to give me The job ID and total salary for the employees doing the job.

It is not expecting me to type all that every time. And it's going to be simple fight query. I don't have to bother about all that. In fact, I can make it even more interesting. What can I do? I can say I want the job title.

And then I would like to join this, you know, we can do natural join with the jobs. And then we say group by job title. Now, this is how I can create. Remember, I'm using our replays so that any changes to the view will be automatically applied. So let's go create this, the view is recreated actually. And now when I say go For this job summary, this time you're going to get job title followed by the total salary.

So, this is how we can create a view with a relatively complex query. And another very interesting thing, you can even select a few roles based on total salary is greater than let us say some 50,000. And this is going to get you the titles where total is more than 50,000. But if you observe it closely, you have something pretty interesting, what is that? Total salary is actually a group functions result and where cannot be used with the group functions, because where is only for individual roles having is what we use for group functions, but in this case, case even though it is derived from a group function, still, we can have a query like this. This is very interesting possibility.

So you're treating this not like anything different, but just like a table. So I view is called as virtual table. It's like a table, though. In reality, it is not a table. Then comes the question. Can we modify a view?

Well, there is no data in the view to modify. But you might be treating view just like a table. So can I use a view for updations, deletions and insertions? The answer is yes, you can, provided the view is satisfying some conditions. So that brings us to the Topic changing base table through view, you can delete rows from base table using view you use view, but the rows will be deleted from base table. Similarly, you can update the data in base table using view and also you can insert but you have to make sure you follow a few rules if you want to accomplish this.

To start with, you have to make sure the view is created with a query which is not referring to any of these options. It should not use distinct the keyboard it should not use any group function are grouped by class are it should not refer to pseudo column row num. So are we satisfying these conditions in the context of it employs Yes, we do. So, it implies view as you can see is not having anything like this thing or group functions or group by. So, it implies can be used to delete a row from this table. So, when you say delete from it implies where employee ID is one not for you are actually deleting the row from employees table.

Similarly, you can even update but the only extra condition is the column you are updating is not supposed to be derived from expression. It is not to represent an expression it should represent a column in the base table. So, this salary is a candidate because it is coming from salary column in the base table. But when you're trying to Update EMP salary, which is created from employees table but total salary is not coming from any single column it is representing an expression. So, we cannot modify total salary and the error message is very clear. It says virtual column not allowed here, because it's not a real column it is a virtual column.

So, you can update this table through view provided these conditions are met. The last option is insertion. Can we insert a row into base table through view? The answer is as provided all the previous conditions are met. Plus, you do not exclude any not null column of this table from the view if you are included All not null columns of base table in the view as you can proceed to insert. Now here it implies is not including all not null columns of base table, it implies is actually excluding email, which is designated as a not null column in the base table.

So, when you try to insert a row like this, it is throwing an error saying cannot insert a null value into email of employees itself because it implies is only referring to employ ID, the first name and last name, salary and hire date. So, at the most I can put values only into these columns. But that means you cannot put value into email and that's where the error occurs. But if you include all not null columns of a base table in the view is it's possible to insert a row as well. So, those are the conditions and this is how you need to handle them. Now, let us see something interesting.

I create a view and I call it as costly employs, and it'll pick up all the rows from the base table, where salary is greater than 10,000. And then I use this view to change the salary of implied to 8000. Is this possible? Because if you make this change, then the employer to not one cannot be retrieved by the view again, because the view is saying it will take only when the salary is greater than 10,000 but if you allow a user to make this change, the salary is 8000 it means you will not be able to retrieve when you execute this query. So what is to be done? Well, by default, or racquel is not stopping you.

So by default this is possible. However, if you don't want this to happen, if you don't want changes to take place in such a way, the view itself cannot retrieve the data, then you have to stop it by using with check option. When we use with check option at the time of creating our view, we are telling a racquel not to allow any changes to base table through view. Remember, through view, okay, you can still make Changes to base table directly. But you can't make any changes to base table through view, as long as the changes cannot be retrieved by view. So in this example, if you try to make a change in such a way, view cannot retrieve that row eight will stop you by giving the message with check option where clause violation.

So, if you don't want any changes to go to base table through view, which the view cannot later retrieve, put this extra option with check option, then Oracle will make sure it won't allow you to make changes which the view cannot retrieve later. So that's about with check option. You have to decide whether to give this or not. Then anytime if you feel view is not needed, you can drop it actually view doesn't consume much space, there is no problem if you have the view. But for some reasons, if you feel the view is no longer needed, simply drop it, it doesn't affect much. And then what if you want to have a view that is storing the data also, because in some applications, we want to take the data from this table and have a copy of the data separately stored, so that we can quickly access that data without accessing the base table.

So how do we achieve that? Oracle provides a slightly advanced sort of feature called materialized view, where it is like a view all that we have seen about view is more or less the same. With this, but here we are storing not just the query, but also the data. So it is used when you are planning to get summaries, or when you're planning to get data from many tables. So, any query that takes time to retrieve the data can be a candidate for materialized view. And here is the difference.

In case of normal view, you see only the query stored in the database. But in case of materialized view, you have the query but you also have the rows related to the table that are selected by the query. So, whatever the query retrieves that is stored along with the query in materialized view, so next time when I retrieve the data, it is not going to go to the base table. Instead, it will just go to the View materialized view and it will get the data. And this is generally done in cases where we have lot of data in the base table and the data is mostly static means the data is not changing frequently. And in such cases we can get the summaries into metallized view so that we can access summaries more quickly.

So, as you can see, it is used mostly in decision support systems where the data is related to past and data does not change very frequently. So, this is about how you can store aggregates and results of joints so that you can access data more quickly in future. So, those are the advantages of materialized view Of course, you have to be aware it does consume extra space. And also, if the base tables change, your view gets outdated, because the data in the view is no longer accurate. Well, you have to understand the pros and cons. But it does improve performance in a good number of cases.

Well, that's about this lesson about views. We understood what is the view? Why do we create a view, it is created to give access to a part of the table so that we can hide irrelevant details from others. And then we talked about changing the base table through view, but there are conditions to be met. And we can ensure we don't change anything in the base table through view which the view cannot retrieve by Using which check option. And also, we understood we can drop view anytime.

And there is a variant of view called materialized view, which is storing data retrieved by the query along with the query. So views don't store data. materialized views store data along with the query. So those are the details you need to know about views. And views play very important role. And we already use two views for that matter.

Whenever we use the user constraints, our user tables, they are all actually views. They internally talk to data dictionary, which is a collection of tables. So we have been using views and we use a lot of them even in future. For now. Just get the concept of the view And we will be using a lot more of it in the future lessons. For now.

That's all you need to know about views. We will resume in the next lesson.