Views
- The purpose of views.
- To create and use views.
- To drop views.
Views are essentially saved SELECT queries that can themselves be queried. They are used to provide easier access to normalized data. For example, the Orders table has information about an order. Although it references the employee and customer involved in each order, the Orders doesn't itself contain any valuable information about the employee and customer. We have seen how to use joins to output valuable data from different tables. Creating a view is a way of saving these types of more complicated queries. Further, you can query views in the same way that you can query tables. You can even join them with other views or tables.
Creating Views
To create views, simply write your SELECT query and wrap it in a CREATE VIEW statement as shown below.
CREATE VIEW view_name AS SELECT statement goes here...
Here is an example showing how to create a view from a complicated query we've seen earlier.
Code Sample: Views/Demos/CreateView.sql
--create view showing detailed order information CREATE VIEW vwOrderInfo AS SELECT o.OrderID, o.OrderDate, o.RequiredDate, o.ShippedDate, c.CompanyName, e.FirstName, e.LastName FROM Orders o JOIN Employees e ON (e.EmployeeID = o.EmployeeID) JOIN Customers c ON (c.CustomerID = o.CustomerID); --Select all records from view that were shipped after the required date SELECT * FROM vwOrderInfo WHERE RequiredDate < ShippedDate
You can see that the process of creating the view is simple. You should also notice how easy it is to get information on specific orders that includes relevant data from the Employees and Customers table.
Note that the ORDER BY clause is usually not allowed in views.
Dropping Views
Don't like your view? Dropping it is easy enough:
DROP VIEW view_name
Benefits of Views
Views have the following benefits:
- Security - Views can be made accessible to users while the underlying tables are not directly accessible. This allows the DBA to give users only the data they need, while protecting other data in the same table.
- Simplicity - Views can be used to hide and reuse complex queries.
- Column Name Simplication or Clarification - Views can be used to provide aliases on column names to make them more memorable and/or meaningful.
- Stepping Stone - Views can provide a stepping stone in a "multi-level" query. For example, you could create a view of a query that counted the number of sales each salesperson had made. You could then query that view to group the sales people by the number of sales they had made.
Exercise: Creating a View
In this exercise you will create a view showing the number of sales by salesperson.
- Open a new query window and create a view that gets the following data:
- Employee first name, last name and id
- Number of orders that employee has made (as NumOrders)
- Write a select statement that shows how many employees have made different numbers of orders rounded to the nearest 10. Your results should look like this:
Views Conclusion
In this lesson of the SQL tutorial, you learned the purpose and benefits of views and how to create and drop them.