SQL Views   «Prev  Next»
Lesson 6 The steps to create a view
Objective Understand the three steps necessary to create a view.

Create View in SQL

Steps to create SQL View

Views are pretty straightforward to create. It takes three steps to create your view, two of which are more for testing purposes than actually creating the view.
  1. Create and test the SELECT statement that the view will represent.
  2. Create and save the new view (or update the existing view).
  3. Test your new view without any additional restrictions to make sure it performs as you expect.
This first step is a simple SELECT statement that retrieves the information you need from the table or tables.
The statement can include joins, WHERE clauses, one or more tables, and so on.
Create and test your script so that it returns exactly what you need. Then you are ready to save the query to the engine. In SQL this is done with the CREATE VIEW statement.

Creating Views

A view is often called a virtual table because the view has the appearance of a table, but the data set is not physically stored in the database but rather pulled from the underlying tables on demand. The view's data set does not exist anywhere until the moment that the view is used. When the view is executed, the DBMS pulls the data and performs any operations or calculations required by the SQL statement, and then it presents that data to the user.
To the user, the view looks exactly like a table, with columns and rows of data, but usually the data is not persisted. A view can be as simple as
  1. a handful of fields from a single table or as complex as a system
  2. of joined tables pulling fields from any or all tables in a database.

How to create a simple view.

1. The process of building a view begins with building a syntactically correct SQL statement.
SELECT LastName, FirstName, Email, DateOfJoining
FROM MemberDetails;

2. Once you have the SQL statement, you can save the view in a database system by adding the following SQL as the top line of the statement:
CREATE VIEW MyViewName AS

3. Executing the following statement causes the database to save the view:
CREATE VIEW MemberNameEmail AS
SELECT LastName, FirstName, Email, DateOfJoining
FROM MemberDetails;

Notice that the CREATE VIEW statement does not pull data from the tables and display it, but rather causes the database to store the SELECT statement as a view named MemberNameEmail.

Sample Data Dictionary Tables

The precise tables that make up a data dictionary depend somewhat on the DBMS. In this section you will see one example of a typical way in which a DBMS might organize its data dictionary. The backbone of the data dictionary is actually a table that documents all the data dictionary tables. From the names of the data dictionary tables, you can probably guess that there are tables to store data about base tables, their columns, their indexes, and their foreign keys. The syscolumn table describes the columns in each table (including the data dictionary tables). In Figure 3-2, for example, you can see a portion of a syscolumn table that describes the DistributedNetworks merchandise item table.

Figure 3-1: A portion of a syscatalog table.

Figure 3-2: Selected rows from a syscolumn table