Introduction to Views

Speaker Notes

Slide 1:

Information on creating views and the need for views.

Slide 2:

A view carries information from one or more tables. It does not physically carry any data, it is simply a way to view data from one or more tables.

Please read the notes on Introduction to Views and Reports for more specific comments.

Slide 3:

The advantage of this view is that the data from two tables is linked together and the results are stored as a view.

Instead of doing the select with the added complications of linking the two tables, you can now do a select using the view name.

Remember views are not permanent data stores. When a change is made to one of the tables, the view will reflect that change.

Slide 4:

Note: No alternation or updating had to be made to the view. I simply added the record to the stucoure00 table and did the select from the view. The new record was incorporated.

Slide 5:

Note that the information that was added to stucourse00 is included.

Slide 6:

Again note the difference in messages with a DESC and a SELECT.

Slide 7:

This slide creates another view using information from two tables. The logic is the same as doing the select for the two tables alone. The difference is the results are stored as a view for future use.

Slide 8:

This slide shows three different selects using the view majorinfo.

Slide 9:

This slide shows a view created using three tables.

Slide 10:

In creating this view, I made lots of errors. I decided to show the process one more time instead of just fixing behind the scenes.

Slide 11:

This view takes information from three related tables and also specifies that the first three characters of stucourse00.coursecd be equal to CIS for all records extracted.

Slide 12:

The * on the current line appears when you have been editing the text of the command.

Note that the final AND on line 7 limited the records in the view to those that contained CIS as the first three characters of stucourse00.coursecd. This is what is in the view. I cannot use this view to see anything about MAR or MAN. They are not a part of the view.

Slide 13:

Note that in the SELECT I can refer to the fields simply by name because those are the names defined in the view. I do not have to be concerned about which column on which table the data is stored.

Slide 14:

You want to think whether there is a reason for changing from names you are used to before making similar changes.

Slide 15:

Notice that I gave names to each of the group totals that I accumulated for this view.

Notice that the count and sum were given default numeric types with the default length.