Editing a view - SQL Tutorial

- [Instructor] Now that we have created our first view, V-Average-Total, there are times when we would want to make changes to an existing view. Now, this is a good time to point out that there are indeed some changes between relational database management systems. I did mention earlier that there is a 5% or so difference from one RDBMS to another. So, with regard to the relational database management system we are using here, which is SQLite, there is a slight difference to let's say, SQL Server, which does have a little bit different way of handling a view. Now, when it comes to the modification of views in SQLite, there isn't a syntax that actually modifies the existing view. Instead, the existing view is actually deleted, then recreated with the modification that you intend to make. To do this modification, we can handle it in a number of different ways. In the browser here, as we have it, we can go ahead and start editing our view but the first thing we must do is drop that view first. So a good way I handle it, at least in the SQLite environment is to go back to our database structure tab and locate the view that we want to change. And if you recall, we did see that there is a right click functionality which gives you a few menu options. So there is a modified view option. When we click this option, we do see a a popup. And this is to just give us a bit of a warning telling us that this action will open a new SQL tab with the following statement. And if you look at the statement the first thing that it starts off with is dropping the view. Let's take a look at this view in the actual execute SQL tab by clicking okay. This has created a new tab and let's take a look at the statement. The only difference with regard to creating a view versus modifying it is this first statement here and this first statement what it does is drop the existing view. Drop is another way of saying deleting it. So the first thing you do is delete and then you modify the existing view. So let's modify this view and make it a little less accurate. So let's remove the nesting and I'm going to remove the round function and remove the closing bracket as well as that two and the comma. So what we remain with is a plain average function which as we know would give us many places after the decimal point. But just for demonstration purposes to modify our view, let's go ahead and run this statement. Just as we did before, we look in our messages pain, and we do see a confirmation that this query has been executed without error and it has been executed successfully. When we go back to our database structure tab we take a look at our view. It looks the same as far as the name, but when we go over here to see the contents of it, we do see that that average total is just by itself, not being surrounded with the round function as it did before. Now we can also go back to our browse data tab, take a look at our view. And this time if we look at the value, it is not rounded to the two decimal places, but it is now showing our modified value without it. So this is the way that we would go about modifying a view in SQLite.