From the course: Microsoft SQL Server 2022 Essential Training

Create a view of the data

- [Instructor] Any time you pull information out of a SQL Server table, you're using a SELECT statement that queries a list of tables and columns, and processes the data according to your requirements. SELECT statements can get pretty complex, especially when multiple table joins and filtering criteria are in play. You can save the syntax of a SELECT statement for future use by creating a view object. To create a view of the data stored in the Red30Tech database, find the Views table in the Object Explorer. Then right click on it, and then click New View. That brings up this Add Table window. I'm going to create a new view that combines data from across all four of our tables in the database. You can either click either one individually and press the Add button at the bottom or highlight the first one, Shift + click the last one to highlight all four, and then press Add. When you do that, you'll see representations for all four of those tables appear behind. Once that's done, you can go ahead and close the Add Tables window. Now we can focus on the View Design window. This designer is split into four different areas. We have the diagram pane here at the top, the criteria pane here, the SQL pane, and finally, a results pane at the very bottom. Up on the toolbar, we have four different buttons that'll toggle each one of these sections on or off. So here's the button for the diagram pane, the criteria pane, the SQL pane, and the results pane. I want to focus on just the diagram pane and the SQL pane. So I'll go ahead and turn the other two off temporarily. In the diagram pane here at the top, we're visualizing the FROM clause of a standard SELECT statement. Then in the SQL pane below, you can see the syntax that's generated based on what we have going on up above. I'm going to rearrange these tables a little bit so we can see all of the fields in each of the tables. So here is the Customers table. That's linked to the Orders table with all of its fields. Then we have the Products table that's linked to the orders. We can go ahead and make this a little bigger. And then finally, over here on the far right, I'll put the ProductCategories table, which we actually didn't link to the Products table yet. But that's okay, we'll take care of that in just a moment. So you can see that the first three tables are all connected by this join line between them. This line represents our foreign key relationship, specifically the primary key column in the Customers table, the CustomerID field there is linked to the CustomerID column, which is the foreign key in the Orders table. Similarly, we have the primary key in the Products table called ProductID. And that's linked to the foreign key in the Orders table right there. The icons here give you more information about each of these relationships. On one side is the primary key icon that we've seen before and on the other side is an infinity symbol. This tells us that each of these relationships is a one-to-many relationship. That means that every customer can have one, none or a large number of orders in the Orders table. Likewise, each product can be found on no, one or many different orders. Now, we never created a formal foreign key relationship between the Products table and the ProductCategories table. And that's why there's no connecting line here. But I do know that there is a data link between these two. We can temporarily create a join between these two tables by finding the fields that hold the related information and dragging and dropping one on top of the other. In our case, it's the Category field in the Products table. I'll just click and drag and drop it on top of the CategoryName field in the ProductCategories table. That'll create our temporary join here. You also might have noticed that it added an additional line into our FROM clause where it's specifying that we're joining tables based off of those two fields. Now we can add columns from these four tables into the SELECT statement. You'll see that it currently selects nothing right down here in the SQL pane. In order to do this, we simply place checks in the boxes to the left of each field that we want in our query. I'll go through it and make a bunch of selections here. I'll choose the FirstName, LastName, Address, City and State from the customers table. The OrderDate and the ProductID and quantity from the Orders table. Then I want the name of the product, the RetailPrice, as well as the category from the Products table, and finally, I want the CategoryID and the CategoryAbbreviation from the ProductCategories table. You can see those all get added down here into the SELECT statement. Now I'll come back up here to the toolbar and I'm going to turn off the diagram pane and turn on the show criteria pane. Now we can see an alternate view of all of the columns that I just selected. This gives us the option of adding an alias to each of the columns in the final results. An alias is simply an alternative name that'll show up in the column header. Here I can specify FirstName with a space between the words. I'll do the same thing for LastName. By adding spaces here, it makes it a little bit more legible in the final results. We'll do the same thing for all of our compound words. So Order Date with a space. Product ID. The Category ID. And finally, Category Abbreviation. After making all of those changes, you'll see the SELECT statement has updated to include the AS keyword, followed by each of the aliases for the different columns. Now, there's some additional options that we have in this upper portion to control the query results. If I add a sort type of ascending to the last name, and then click off of it, you'll see that we get an ORDER BY clause added down to the query. And finally, at the very end, we have a number of columns that we can use to supply filtering criteria in the filter and Or rows if we want to build that into the query as well. So when you're done building out your SELECT query, you can press the Execute button up on the toolbar. It's this button here for our window that we have right now and it's Control + R on your keyboard. That'll execute it and open up the result panel down here at the very bottom. So you can see the results are pulling information out of all four of our tables and displaying it in a single row. So now let's go ahead and save our view. You can either press the disk icon on the toolbar or press Control + S on your keyboard. I'll name this view OrderDetails. I get this warning about using the ORDER BY clause in our view, and that's okay. Just go ahead and press the OK button to dismiss the window and our view is now saved. I'll close the tab. Now we can take a look in the Object Explorer and expand the Views folder and there's the new view that we just created. So now that we have one, what can we do with a view? Views in SQL server are treated just like read-only tables, only they don't store any data, they simply refer to the original columns in data from the original data tables. The benefit to using them is that they save you the hard work of using joins and other complex SELECT query syntax when you need to review common arrangements of your data. For instance, I can come up and start a brand new query. Then I'll type SELECT *. Now instead of specifying specific columns and tables, I can just pull the data from the view. I'll say SELECT * FROM OrderDetails. When I execute the query, the view gathers all of the information from the original four data tables and presents it as if I had just queried those four tables. This gives me all of the columns from the four different data tables, all properly joined together without me having to write out the full SELECT statements that I would need if I were referring to the tables directly. So as you build out your database and create a number of related tables, remember to create views that piece information together from those related tables in the arrangements that you want to see regularly.

Contents