Views are a highly useful tool for abstracting how you see the data stored in tables. At their simplest, they are just a stored query that can be run without needing to know the contents of the query itself. Common uses include:

• Joining tables together
• Restricting data that can be viewed
• Performing aggregates and calculations

 
So for example, if you have a couple of tables containing information about orders, Order (who placed it, when, where, how did they pay etc) and OrderDetail (what was purchased, how many of each type, what was the cost etc), you could write a query that joins the two tables together, displays only certain types of orders (pending maybe?) and does some calculations (total sales for the order etc). But if every time you need that information you have to look up that query and run it then you will increase your risks of mistakes as well as maintenance (if that query is in several pieces of code and you have to change it for example). However, if you save the query as a view then anyone who needs that information can just query against the view and any changes to the query can be made from a single place.

So here is the homework for this month. Create a view.

• Review the code behind two or more of the existing views in whatever sample database(s) you set up last month. (20 points)
• Create a view using a single table. (20 points)
• Create a view using two (or more) tables from AdventureWorks, Wide World Importers or some other sample DB or create your own tables. (30 points)
• Create a view with some form of data filtering (a WHERE clause). (15 points)
• Create a view with at least one aggrigate (SUM, AVG, etc) and one calculation (Percentage, Age, etc). (15 points)

 
If you want to combine some of these feel free. You could do a single view that queries a table, has a WHERE clause, and does some aggregates/calculations. Or you can do 4 separate views. Either way is fine.

Methods:

• GUI – Personally I find this more difficult but if you want to go this route feel free.
• Script – If you are used to using the GUI you might give this a shot anyway. With a little practice, I’ll almost guarantee this will be easier.

And as always this is meant to be an exercise for those new to database work, or who haven’t had a chance to work with this particular tool. If you have created dozens or hundreds of views in your time and can already create a view in your sleep then you don’t need to worry about this one.