RelationalDBDesign 




Advanced SQL   «Prev  Next»
Lesson 1

Introduction to SQL Views

Views allow you to create a stored way of looking at the information in your table. When you create a view, you create a way of querying the database table not only today, but also in the future. You can come back to a view and recall it, producing updated results.
In this module, you will find out how views are created, how they apply to your query and reporting requirements, and how you can use views to make your queries easier in the future.
Since views are a server- or engine-based element, they are can be heavily engine-dependent. There may be some slight differences in how views are implemented in your engine, and those differences will be called out whenever possible during these lessons.
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows.
Rows updated or deleted in the view are updated or deleted in the table the view was created with.
It should also be noted that as data in the original table changes, so does data in the view.
This is because a view is not really a table itself, but only a way to look at part of the original table.

Views, as the name implies, are queries used to provide users with a specific view of the data in the database. In doing so, views serve a variety of purposes. They may tie related tables together and then pull selected fields out that specific users need, or they may pull selected interesting rows of data from a table that contains other rows of no interest to specific users. They may summarize
  1. large data sets,
  2. returning averages, and
  3. counts.
Relational databases are sets of data in tables, which, taken alone, are rarely of any use to anyone. Views allow the database administrator (DBA) to pull fields of interest from tables of interest and return a coherent data set useful to some specific user or application. In this chapter, you learn how to build and save views for reuse, how to use views to tie related data back together, how to use views to narrow or limit the data that users are allowed to see, and how views may be used for security purposes.

Views (Virtual Tables) in SQL

In this section we introduce the concept of a view in SQL. We show how views are specified, and then we discuss the problem of updating views and how views can be implemented by the DBMS.


Concept of a View in SQL

A view in SQL terminology is a single table that is derived from other tables. These other tables can be base tables or previously defined views. A view does not necessarily exist in physical form; it is considered to be a virtual table, in contrast to base tables, whose tuples are always physically stored in the database. This limits the possible update operations that can be applied to views, but it does not provide any limitations on querying a view. We can think of a view as a way of specifying a table that we need to reference frequently, even though it may not exist physically.
For example, referring to the COMPANY database in the table below we may frequently issue queries that retrieve the employee name and the project names that the employee works on. Rather than having to specify the join of the three tables EMPLOYEE,WORKS_ON, and PROJECT every time we issue this query, we can define a view that is specified as the result of these joins. Then we can issue queries on the view, which are specified as singletable retrievals rather than as retrievals involving two joins on three tables.
We call the EMPLOYEE,WORKS_ON, and PROJECT tables the defining tables of the view.

EMPLOYEE
Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Dependent_name Sex Bdate Relationship