SQL Homework Nov 2017: Create a view

2

November 1, 2017 by Kenneth Fisher

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.

2 thoughts on “SQL Homework Nov 2017: Create a view

  1. denisgobo says:

    Back in the day when partitioning didn’t exist, I used partitioned views and that worked very well for me. I still use views for some queries that are used in many places that is way if a change is needed it has to be done in only 1 place. But I have seen some horrible views in my lifetime. And then you have the EM designer that used to create TOP 100 PERCENT .. ORDER BY nonsense

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: