Create a database view

- [Instructor] When it comes down to it, views are simply saved queries that are stored in the database and they provide a quick and secure way of exploring your data in various configurations. This gives you a big clue as to how to add them to your database. First, we need to start with our good friend, the standard select query. The file CreateView-Start in the exercise files includes this query that pulls information from a number of tables in the two trees database. The syntax is exactly the same on both the SQL server and Postgres platforms. So we can run this on either database successfully. I'm going to demonstrate this on the Postgres SQL database. So right click on the two trees database in the Postgres server, choose new query, and then paste in the text from the exercise files. Let's run this query to see what it returns. This data provides a detailed look at each line of every order that's been placed. We can see the order ID number, a row for each line item in the order, who placed the order and when, and some information about the products that were ordered over here on the right. This query can be really useful in exploring the data that split up across four different data tables in the two trees database. But it's a lot to write out every time you need it. You can save the text of the query to an external file as I've already done with the exercise files. But then you're managing external resources alongside of your database. The best option is to save this query inside of the database. And that's exactly what a view does. To turn this select query into a database view, we need to add a single line to the beginning. I'll click my mouse cursor here, the very beginning of line number one, and move everything down so I have some room to type. To turn this into a view, we need the create view command. Next, we'll provide a name for the view. We named the view following the same rules as when we named data tables. And we want to continue organizing our database objects using schemas. Since this query is currently returning detailed information about all of the products ordered by each customer, it makes sense to put this view in the same sales schema with the data tables about our orders. So I'll create the view in the sales schema and I'm going to call it order details. Views can contain almost all of the same clauses as a select query. The only clause that you should not include in a view is the order by clause. Since the sequence that rows are returned in will be determined later. So make sure that you omit any order by clauses from your select queries when you turn them into a view. So online number one, I've got the command create view. I've named the view, and then I need the keyword AS. After that, we follow it with the entire contents of our select statement. This time when you run the command, we don't get a results grid because we're no longer asking for information to be returned. We get the message that the object was created instead. To find the view in the sidebar, expand the views folder underneath your database. You'll see that new view listed right there. You'll see that it gets presented just like a data table. And in fact, we also have a columns folder that lists out all the different columns that the view is collecting from our main data tables. But we know that these are just virtual pointers back to the original data sources. If you ever need to remind yourself how the view is created, you can right click on the name and choose script as create from the dropdown menu. That will open up the command that created the view so that you can see what tables and columns are used and how they were related in the views definition. You can also remove a view from the database with a simple command if you want to get rid of it. I'm going to right click on the view again, and this time choose script as drop. This will show you the one line command that'll remove it from the database. It's simply drop view, and the name of the view. Okay, so let's close both of these tabs and return back to our original script. Now we can make use of this view. Let me come down here and give myself some more room, and come down to line number 19. First, we can pull all of the columns and rows from the view with select star from sales.order_details. If I run just line number 19 by highlighting it first, you'll see that we get back exactly the same information that we got with the much longer select query. That's because this view is doing all of the hard work of gathering the requested rows and joining the tables together. Just like when querying tables directly, we can use a where clause on the view to filter the rows that are returned. For instance, if I'm only interested in seeing the items purchased on a specific order ID, I can add a where clause to our select statement here that says, where the order ID is equal to 105. This time when I query the view, we'll see that order number 105 had three different line items on. Or if I'm interested in seeing all of the order details for a specific company in a reverse chronological order, we can change the where clause to say where the company is equal to Wild Rose, for example, and I can add an order by clause. Let's order by the order date, descending. This will make it easy to see that Wild Rose's most recent order was number 114. It included a single product. There's only one line for order number 114. This included five bottles of the Extra Virgin Olive Oil in the 128 ounce size. So that's how views work. So they don't store data on their own, they only point back to the original data source. And they'll always present the most up-to-date values stored in your tables. They allow you to save complex select queries right inside of the structure of your database so that they're easily accessible by any of your database users.