Hello, and welcome to this lecture on views. What are they? How do you create them? What you might or how you might use them. Now this person here wants to be able to access the database but does not want to bother with all of the joining stuff that we have so carefully gone through. So this person here does not need to do the joins in order to retrieve data for the company, contact and sales tables from our database. Because this person here, particularly with her shoes off, has written this query and then saved it into the database as a view. The syntax is in DDL Dynamic Data Language. And it is indeed create view and then you have to give it a name. Much the same way as you give a table a name. In fact, views and tables share the same name space. In other words, there must be something which doesn't already exist as a view or a table so that the naming is unique. Well, the reasons for this is because once the view exists, it can be used just as if it were a table. Because it was a table. Anyway, it can be used in place of a table. A view is a safe query in simple terms. But it saves the select statement. Another difference is there's no data in a view. Data's in the tables. Now, the standard is that you cannot have an order by clause in a query, a Microsoft SQL server and in mySQL, you can in fact put an order by clause in there. Whether it's a good idea, I think is another matter. Because I'm sure what you will do is you would then sort on that order by clause. And then if somebody have their own order by clause, it will then resort. So generally, you're better off not sorting the view and just allowing people to choose the order in which they wish to see things return. You can qualify the start with a table name or alias. So this can be Pete Roses sales table along with the company and contact names. In this result set, I have both the salesperson and the department sales targets. Different sales targets, one for the salesperson table, one for the other table. And this is already potentially confusing, isn't it? As a result set. Because you have to refer to the query in order to see which is which but when it comes to using that to create a view, it's a rather big error. This is a view and so it must be unique. 

Column name, sales_target in view or function, this is a view. Salesperson_dept is specified more than once. What I'm saying is I can't have two columns in my view called sales_target any more than I could have two columns in a table called sales_target. The way I fix this is to give it an alias. And technically, I only have to alias one of them. So, we'll alias dept_target. You can choose you own names if you have an error, particularly in a select statement. The first thing I would check for this is to see if I had any extraneous or miss in commas. I have now dropped that view. What has happened to the data? Nothing. Yes, nothing has happened to the data because views did not have the data. The data is in the tables. The tables are still there. It's only the view that can be moved but that can be created. I can also treat it like a table in a sense that it is a thing which has an employee number column in it. So I can link this view, join this view in the sales table. It's quite like a table but it's basically a shorthand for this query here. This select statement. This statement would also fail because this one has no column name. Because it's a calculation, a ratio of salesperson to department sales target. Again, we'll need to alias that. If you have any ambiguous columns or duplicate column names or if you have expressions that don't have a name, that's the column name now in that view. It's the column alias. This is quite possibly the main reasons why views exist. And you might be using views yourself for this very reason. Another reason is security. It's a way of providing access to some other data to many of the people. So you know, DBA obviously has access to everything. The developers will probably have access to everything on the test database. And then you have users who are not DBAs or not developers. But they are people like your sales who are needing to access real data. But maybe not all the real data. It might be that views can give you access to data rather than the base tables. So here, somebody has created a view. Here, we have an employee table with some information which is not at all sensitive and some information which is considered sensitive. 

The idea here is that this non-sensitive information, anyone who has access to these databases can easily be given access to this view. Access to the underlying table, however, should be restricted to certain privileged users. This view simply needs to be set up by one of those privileged users. People can be given access to that view. They do not need direct access to the underlying table which is how to sell which security. The idea here is a table has become so large that to make maintenance easier, you might have some information being hit regularly and some not. It may also be that some information is more frequently needed. So here is how we might go about making our database more performant and easier to maintain. The first step would be to tag it back up of the databases. Step two is to create two tables called Emp1 and Emp2. And then by using the insert with the select some text, take data from the Emp table and populate the Emp1 and Emp2 tables. The ultimate goal, by the way, is to make it so that people don't have to know that something's changed. So having created those two tables and populated them, the next step is to drop in table Emp, followed by create view Emp. Followed by create view Emp using a select statement to join these two tables together on the ID column. This will be a one to one. And return those columns in their order. Anyone looking at this view and running select statements against the Emp view is now running and gives a view called Emp rather than a table called Emp. The users are unaware potentially. The people or parts of the application that would need to be informed or changed would be those that are dealing with changes to the data. Because although, you can use a view to manipulate data in a table, you can't use a view to manipulate data in two tables in one statement. So, if you were to try and insert into Emp now, it will not work because you actually have to insert separately into the Emp1 and Emp2 tables. While having smaller tables and queries would be quicker because the rose would be shorter. The other issue here is yet again, DBA needs to know what he or she is doing because there's not going to be any checking on these views and updates. If table three was dropped, view C would still exist but would be broken. There's no kind of referential integrity check equivalent as there is with removing rose from a table. And indeed, removing tables. If we wanted to empty all of the tabled data here, in order to drop all the tables from our feed map, we'd have to start with a sale table and then we could get rid of the contact and then company. And then salesperson and department. Because of the way the relationships work. But with this, if you can get rid of a table, you've just broken the view. 

You have create and drop and alter an SQL server and mySQL. In Oracle, the syntax is create or replace. This is how you can change the select statement that is underlying the view. Now you don't have the equivalent of the alter table statement whereby you'd say alter table add a column. What you can do, is if you alter a view, you have to give it an entire select statement. And if you wanted to effectively add a column, then you'd simply add a column to the select list. So we'll call this sp_tel. And that's basically a replacement rather than an alteration. Typically, you create a view and then you give access to that view to various people. If you drop the view, all of the information, which is something that alters views as well. And by using the alter view statement, then those privileges are maintained. One thing here is I'm creating a view which is a select statement with a filter within a where clause. So this is only going to return those salespeople that are in department three. The manager of department three or the application that the department of manager three users should have access to those view rather than the underlying salesperson table. So this view is not actually going to insert a row into department three's staff table. Dept three staff is a view. However, you might already have spotted a problem. I'm specifying here a column list of Emp underscore a no. First name, last name and dept no. So put that on the end. You will note perhaps the department number there is not three but two. So this manager here is about to add Fred Bloggs as a salesperson. But he's about to be in sales department too. Which effectively is the wrong sales department. It works. However, having done that, when the manager reruns that query, Fred Bloggs is nowhere to be seen. Now to be fair, the manager might think maybe I didn't run that statement or perhaps I didn't run that part of the application. They might try it again. And then, get an error. A duplicate key, which will create the error. So what the manager will do now is talk to the DBA so the DBA makes a new spot in department two, the DBA says to the manager, you put Fred Bloggs in the wrong department. The manager says because you haven't created the view properly. When you created the view, you should have created with the check option. So what does that mean? What it means is that the where clause also functions as a check constraint. 

In other words, if this view is used to insert into the salesperson table, or to manipulate a row in the salesperson table by an outdate statement, by an update statement, the department number must always be three. Now obviously, I, as the administrator, I have already created that view. And I will alter it therefore, but I should have created it in the first place with that. Every time you alter this view, you need to include that. Because if you don't, it means you're putting it off. So, the DBA does that. The DBA wants the manager to retry the statement. Now, of course, the issue of the moment, is it gives a duplicate key error. Because Fred Bloggs already exist in department two. How am I going to get rid of that row? I could go for last name equals Bloggs or I think the employee underscore number column is the obvious one to go for. It's going to be employee underscore number 70. So that's got rid of that row. So the manager goes back to the office, reruns this and gets an error message. This is the error message which you will be looking at. If you put the columns in a different order when you create the view, you can potentially get a different error. The attempt to insert or update field failed because the target view either specifies with check option or spans a view specifies with check option. Now remember, you can have a view based on a view. And one or more rows resulting from the operation did not qualify under the check option constraint. So that's a very long, explicit error message. So it is possible to use a view to insert into underlying tables. And to use that view where close conditions to act as a check constraint that may be used in some applications. Okay. So that's using views with a check option. Okay, so that ends our lecture on views. Close this and I'll see you in the next one.