Notes on Views

What is a view?

How to Make a View

There are two ways to make a view.

1. Programmatically.
   
   We will introduce the programmatic option a little later in the quarter after you have some SQL experience under your belt.
   
   
2. In the GUI Design Mode.
   
   You may also right click on the "Views" folder in your database and select "New View...".
   
   
   This will open up the view designer tool. This tool will usually show you four panes:
   (1) First pane: of the selected tables,
   (2) Second pane: a grid lising included fields with alias, sort order and filters for each field, and
   (3) Third pane: the resulting SQL statement.
   (4) Forth pane: results of running the view.
   
   
   The developer can make changes in panes 1, 2 or 3.
   • Select the table(s) you need for your view, click close when you are finished.
     • A NOTE ON VIEWS WITH MULTIPLE TABLES: If your view is returning many more records than expected, the View designer tool in Management Studio may have created a CROSS JOIN, which is causing the problem. In general, if the foreign keys in the related tables are set correctly, once you place the tables in the first pane, there is a line for a pair of related tables. If there is a case that a table is not connected with any other table, that is an indication of (1) the foreign key has not been set correctly or (2) you should place another table that serves as a bridge between the existing tables in the pane. If there are two or more tables in the first pane for you view, every table should be connected with at least one another table. Otherwise, the CROSS JOIN may happen.
     • TIP: Always check the SQL code (3rd pane) for "CROSS JOIN". If you find this, consult with your instructors for a solution. 
   • Clicking the checkbox next to the field names in the tables in the pane #1 will cause them to appear in your select clause of the generated SQL. As you select fields, they will show up in the table in the pane #2 too. In the pane #2,
     • The Alias column allows you to change the field name that appears in the result.
     • The checkbox in the Output column lets you choose if you want the field on that row to be displayed in the result. You can select a field from a table in the Column column, uncheck the checkbox for the field in the Output column, then that field will not show in the result. For example, sometime, you just want to use a specific field in the WHERE clause.
     • The Sort Type column allows you to sort in ascending or decending order. Including any sorts will add the ORDER BY clause to your SQL statement.
     • If you have sorts on multiple fields, the Sort Order column allows you to determine which order they are sorted by.
     • Including any Filters will add the WHERE clause to your SQL statement. If you are specifying specific parameters for your query, (example: Price > 10, City = 'Berlin') this is where you would do it.
     • You can also edit the SQL directly in the designer tool's SQL pane (the pane #3). If you edit the SQL directly, the selections in the pane #2 will change accordingly.
   
   To use GROUP BY or other aggregation functions, click the Group BY button in the top toolbar (see the image below)
   • The Group By column will be added to the pane #2, which does not show by default (see image above)
   • When Group By column is added, all selected fields are automatically changed as the Group By fields with the Group By selected in the Group By column of the pane #2. You can change the Group By to other clauses, such as WHERE or other aggregation functions, such as COUNT (see the examples in the image below)
   • In the example below, We want to see how many SKU (product) in each order that was placed in year 2015 from the stores in the zip code 98110. Therefore, we use OrderYear and StoreZip as conditions in the WHERE clause (choose Where in the Group By column for the corresponding fields); we group the records by OrderNumber (choose Group By in the Group By column for the OrderNumber field) and count SKU (choose COUNT in the Group By collumn for the SKU field) in each set of records that have the same orderNumber. Also, study the generated SQL statement to see how the choices made in the pane #2 generate the SQL statement in the pane #3.
   • Pressing the Execute SQL button at the top of the screen will execute the query and allow you to test it.
   
   
   
   To save the query, right click on the tab and click save. This will save it in the views folder of your database.
   
   The designer tool is useful, especially when you are unfamiliar with SQL syntax. However, it is also sometimes difficult to create more advanced, complex views because of limitations of the designer tool. It can't do everything, such as UNION instructions (which you will learn about in your lectures and individual homework problems, but not in the SCA project).

End of Instructions