SQL Homework Nov 2017: Create a view3
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.
- 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.
Category: Microsoft SQL Server, SQL Homework, SQLServerPedia Syndication, T-SQL | Tags: microsoft sql server, SQL Homework, views
3 thoughts on “SQL Homework Nov 2017: Create a view”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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
Oh sure. Views are really easy to abuse. Still an important tool though 🙂
[…] homework of the year. Last month was to create a view. This month it’s time to take a look in the log! No, not the transaction log. The SQL Server […]