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

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 module 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.

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

Creator Tname Dbspace Tabletype Ncols Primary_key
SYS SYSTABLE SYSTEM TABLE 12 Y
SYS SYSCOLUMN SYSTEM TABLE 14 Y
SYS SYSINDEX SYSTEM TABLE 8 Y
SYS SYSIXCOL SYSTEM TABLE 5 Y
SYS SYSFOREIGNKEY SYSTEM TABLE 8 Y
SYS SYSKCOL SYSTEM TABLE 4 Y
SYS SYSFILE SYSTEM TABLE 3 Y
SYS SYSDOMAIN SYSTEM TABLE 4 Y
SYS SYSUSERPERM SYSTEM TABLE 10 Y
SYS SYSTABLEPERM SYSTEM TABLE 11 Y
SYS SYSCOLPERM SYSTEM TABLE 6 Y

Figure 3-2: Selected rows from a syscolumn table
Figure 3-2: Selected rows from a syscolumn table
Creator Cname Tname Coltype Nulls Length Inprimarykey Colno
DBA item_numb items integer N 4 Y 1
DBA title items varchar Y 60 N 2
DBA distributor_numb items integer Y 4 N 3
DBA release_date items date Y 6 N 4
DBA retail_price items numeric Y 8 N 5

SEMrush Software