Creating a view - SQL Tutorial

- [Instructor] As I just mentioned, if you find yourself repeatedly constructing the same query, particularly if this query is complex or difficult to write, then it's worth your while to start looking at views. Now, officially, a view is referred to as a virtual table. And all of the SQL statements that we've been introduced to thus far in our course can all be saved and reused when we create a view out of them. Now, let's take a look at our query in front of us, which we visited a few times in the past. What this query is doing is just simply getting us the average total amount for our invoices. When we run this query, we do see that we have a total of $8.06 as we visited prior. We can turn this particular SQL statement into a view by means of some particular keywords. Let's go ahead and do this now. To start, let's go ahead and push our query down a little. And above the SELECT, we're going to introduce a new keyword, and that is CREATE. And what we want to create is a view. So we put that keyword as well, CREATE VIEW. Now, we have the two keywords, CREATE VIEW. Next, we need to say what would we like to name this view? Now, it's a usual best practice to name views, starting with the letter V, then we place an underscore next to this V. And next, we should create a name that is meaningful to the function of this view. So in our current example, this view is giving us the average total, so let's name the view, the AverageTotal. We'll abbreviate average and say Avg, and Total. Now we have CREATE VIEW, the view name, which is written in a best practice manner, V_, and a descriptive name. Then the last key word we want to include is AS. Now with this, we have a complete syntax that is now going to take our regular query and turn this into a view. Let's go ahead and hit Run. Something is a little different from previous queries we've run. We don't see a result. But what we do see, and this is the thing we need to look out for when we're creating views, is the confirmation message that this was actually created as we would expect. "Execution finished without errors. Query executed successfully." Now, that indicates that our view was created well. And how do we verify this view? Well, if we head over to our Database Structure tab and we take a look under the section titled Views, we now do see that we have an item there and that is our newly created view, V_AvgTotal. And just like any other object in the Database Structure tab, we can expand this view and have a look at the column inside of it. Just like tables, we can head over to the Browse Data tab and have a look at our newly creative view. Now, if I expand this table selection here, we do see that we have a view now available to us that we can select. And selecting that view displays the contents of it. And in this case, we're actually displaying the average invoice amount that our view is calculating. Now, one final thing to show about views here. If we head back over to our Database Structure tab and we go and highlight our newly created view, if we right click this item, we now see available to us a few different menu choices. We can browse, modify, and delete a view via this menu. All right, great. Up next, let's take a look at how we would go about modifying an existing view.